Convert array of json objects into json

Question

In a postgres table I have some jsonb in this format

[{"type": "pet", "animal": "cat"}, {"type": "farm", "animal": "cow","colour": "brown"}]

But I want to turn it into this format

{"cat": {"type": "pet", "animal": "cat"}, "cow" {"type": "farm", "animal": "cow", "colour": "brown"}

And I can't figure it out or find anyone on the internet who has jsonb in this format. Can anyone solve this? Ideally the sets of data that don't have a key/value for "colour" won't have {"colour": null} but just won't have the key "colour" at all

I'm on postgres 9.6

GMB · Accepted Answer · 2020-06-12 15:15:42Z

1

Here is one option that works by unnesting the json array and reaggregating it into one object:

select x.new_js_col
from mytable t
cross join lateral (
    select jsonb_object_agg(obj ->> 'animal', to_jsonb(obj)) new_js_col
    from jsonb_array_elements(t.js_col) as x(obj)
) x(new_js_col)

This assumes that you have a jsonb column called js_col in table mytable.

Demo on DB Fiddle:

| new_js_col                                                                                             |
| :----------------------------------------------------------------------------------------------------- |
| {"cat": {"type": "pet", "animal": "cat"}, "cow": {"type": "farm", "animal": "cow", "colour": "brown"}} |

answered Jun 12, 2020 at 15:15

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Convert array of json objects into json

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