How to convert json to string by concatenating certain keys in postgres

Question

Input:

[{"name": "X", "strength": "10"}, {"name": "Y", "strength": "30"}]

Desired output:

X-Y 10-30

Aseem · Accepted Answer · 2022-09-07 06:57:10Z

1

First aggregate into arrays and then convert the arrays to strings.

select array_to_string(array_agg(j ->> 'name'), ','), 
       array_to_string(array_agg(j ->> 'strength'), ',') 
from jsonb_array_elements
('[
   {"name": "X", "strength": "10"}, 
   {"name": "Y", "strength": "30"},
   {"name": "Z", "strength": "20"}
]') j;

When doing on a table column:

select col1,
       (select array_to_string(array_agg(j ->> 'name'), ',')
        from json_array_elements(cast(col1 as json)) j),
    (select array_to_string(array_agg(j ->> 'strength'), ',')
        from json_array_elements(cast(col1 as json)) j)
from table1;

edited Sep 7, 2022 at 6:57

Aseem

6,9578 gold badges57 silver badges96 bronze badges

answered Sep 6, 2022 at 9:47

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

How to convert json to string by concatenating certain keys in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related