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

Input:

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

Desired output:

X-Y 10-30

CodePudding user response：

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;