json key:...,value:.... to value_key:value_value in postgresql

Question

how can I change a list of jsonb structure (key/value) to object, dynamically??

for example:

from

[{'key':'size','value':'200'},{'key':'pound','value':'200'},{'key':'square','value':'true'},...]

to

{'size':'200','pound':'200','square':'true',....}

select jsonb_array_elements( topi.other_information)->'key' as key_field,
jsonb_array_elements( topi.other_information)->'value' as value_field
from items_data.tbl_items topi

but when I use jsonb_build_object i need to know the keys. Is there a way to do it?

score 3 · Accepted Answer · 2020-12-04 14:00:21Z

3

You need to unnest the array then aggregate it back using jsonb_object_agg()

select t.id, jsonb_object_agg(x ->> 'key', x -> 'value')
from items_data.tbl_items t
  cross join jsonb_array_elements(t.other_information) as x(element)
group by t.id

t.id is the assumed primary key of the table which is needed to keep the values for the same row together during unnesting and grouping.

If you need that frequently, maybe it makes sense to write a function:

create function flatten(p_input jsonb)
  returns jsonb
as
$$
  select jsonb_object_agg(x ->> 'key', x -> 'value')
  from jsonb_array_elements(p_input) as x(element);
$$
language sql
immutable;

Then you can use it without a group by:

select t.id, flatten(t.other_information)
from items_data.tbl_items t

edited Dec 4, 2020 at 14:00

answered Dec 4, 2020 at 13:57

user330315

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

3 Comments

S-Man Over a year ago

Because I just finished the same solution: here's my fiddle dbfiddle.uk/…

sharkguto Over a year ago

.... i was trying to do the same thing with "with"... ``` with temp_table_items as ( select id,jsonb_array_elements( topi.other_information)->>'key' as key_field, jsonb_array_elements( topi.other_information)->>'value' as value_field,item_number,group_number,quantity,unit_value,total_value,brand, model_version,manufacturer,item_final_details from items_data.tbl_items topi ) select id,jsonb_build_object('is_foreign', case when key_field='is_foreign' and value_field = 'true' then true else false end...``` thank you so much!!!! it works nice your query is much better. thanks!

sharkguto Over a year ago

stackoverflow.com/users/330315/a-horse-with-no-name thank you! you save my day

Collectives™ on Stack Overflow

json key:...,value:.... to value_key:value_value in postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related