CODEWITHSUNDEEP
arraysjsonpostgresqlselectunnest
DatabaseShouter
DatabaseShouter

Reputation: 945

Convert array of json objects into json

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

Upvotes: 1

Views: 126

Answers (1)

GMB
GMB

Reputation: 222402

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"}} |

Upvotes: 1

Related Questions