CODEWITHSUNDEEP
postgresql
Aseem
Aseem

Reputation: 6779

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

Input:

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

Desired output:

X-Y 10-30

Upvotes: 0

Views: 179

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

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;

Upvotes: 1

Related Questions