Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select '{1:"abc"}'::jsonb;

ERROR:  invalid input syntax for type json
LINE 1: select '{1:"abc"}'::jsonb;
               ^
DETAIL:  Expected string or "}", but found "1".
CONTEXT:  JSON data, line 1: {1...


create table my_table(jdata)as values 
  ('[{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]'::jsonb)
 ,('[{"id": 1, "name": "John"}, 
     {"id": 11, "name": "Jane"}, 
     {"id": 111, "name": "Bob"}, 
     {"id": 2, "name": "Ted"}]'::jsonb);

SELECT 2


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



SELECT 2


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


jsonb_object_agg


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

{"1": {"id": 1, "name": "John"}, "2": {"id": 11, "name": "Jane"}, "3": {"id": 111, "name": "Bob"}, "4": {"id": 2, "name": "Ted"}}



SELECT 2


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



UPDATE 2