Search code examples
sqljsonpostgresqlplpgsqljsonb

Postgresql: From array of jsons to a single, id-indexed json

In a SQL function I have an array of jsonb in the format:

[{id: 1, name: John}, {id: 2, name: Jane}]

I would like to transorm this structure to a single jsonb using the ids as keys:

{
  1: {id: 1, name: John},
  2: {id: 2, name: Jane}
}
Solution

  • You can explode the array with jsonb_array_elements(), then re-aggregate to an object with jsonb_object_agg(). Demo at db<>fiddle:

    select jsonb_object_agg(_element->>'id', _element)
from my_table
cross join lateral jsonb_array_elements(my_table.jdata) as _an(_element)
group by my_table.ctid
    jsonb_object_agg
    {"1": {"id": 1, "name": "John"}, "2": {"id": 2, "name": "Jane"}}
    {"1": {"id": 1, "name": "John"}, "2": {"id": 2, "name": "Ted"}, "11": {"id": 11, "name": "Jane"}, "111": {"id": 111, "name": "Bob"}}

    You can't use a JSON number as a key, which is why I used ->> accessor casting to text. Not knowing your primary key, I'm grouping by system column ctid because it's always present and unique, but you probably want your actual PK. The cross join lateral can be replaced with a single comma , but explicit join syntax is typically preferred over old, implicit style.

    If you didn't want the actual id but rather the index in that array to be the new key, you can use with ordinality to get those:

    select jsonb_object_agg(n::text, e)
from my_table 
   , jsonb_array_elements(jdata) with ordinality as elements(e,n)
group by my_table.ctid

    Same, but as an update rather than a select:

    update my_table set jdata=
(select jsonb_object_agg(e->>'id', e)
 from jsonb_array_elements(jdata) e )
returning jsonb_pretty(jdata);
    jsonb_pretty
    {
        "1": {
            "id": 1,
            "name": "John"
        },
        "2": {
            "id": 2,
            "name": "Jane"
        }
    }
    {
        "1": {
            "id": 1,
            "name": "John"
        },
        "2": {
            "id": 2,
            "name": "Ted"
        },
        "11": {
            "id": 11,
            "name": "Jane"
        },
        "111": {
            "id": 111,
            "name": "Bob"
        }
    }

    Note that jsonb array always keeps its original order as significant, but the resulting jsonb object will be freely reordered by PostgreSQL, based on keys.