How to concat strings in a jsonb array in a psql query?

Question

I want to dynamically concat strings in a jsonb array in my psql query which is as follows :

with cte as (select generate_series(1, 3) as num) select '["name", "number"||cte.num]'::jsonb as res from cte;

But I am getting this error :

ERROR:  invalid input syntax for type json
LINE 1: ...e as (select generate_series(1, 5) as num) select '["name", ...
                                                             ^
DETAIL:  Token "|" is invalid.
CONTEXT:  JSON data, line 1: ["name", "number"|...

So, here I am generating numbers from 1 to 3 and I want to append those numbers with "number" string in my jsonb array. This is what I want in my result:

         res                 
----------------------
 ["name", "number1"]
 ["name", "number2"]
 ["name", "number3"]

Please tell me how do I get this. Thanks.

@no_one- you can try concat_ws() Function.

Zakir Hossain
– Zakir Hossain

2020-01-13 16:30:58 +00:00
Commented Jan 13, 2020 at 16:30 — Zakir Hossain
– Zakir Hossain, Commented Jan 13, 2020 at 16:30

MkWTF · Accepted Answer · 2020-01-13 16:30:59Z

1

Something like this should work.

SELECT ( '["name", "number'|| num ||'"]' )::jsonb as res 
FROM generate_series(1, 3) num

You were adding the pipes as characters after an element in a list. You should first build the array string (in parenthesis), and then convert it to jsonb.

answered Jan 13, 2020 at 16:30

MkWTF

1,3827 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JessePinkman Over a year ago

Thanks @MkWTF, this helped. So, now I have rewritten my query as : with cte as (select generate_series(1,3) num) select ('["name", "number' || cte.num || '"]')::jsonb from cte;

Collectives™ on Stack Overflow

How to concat strings in a jsonb array in a psql query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related