CODEWITHSUNDEEP
postgresqlpsql
JessePinkman
JessePinkman

Reputation: 651

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

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.

Upvotes: 0

Views: 484

Answers (1)

MkWTF
MkWTF

Reputation: 1372

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.

Upvotes: 1

Related Questions