I have json column with data that looks like this:
CREATE TABLE foo(mycol)
AS SELECT $${
"a": [
{ "q": 5 },
{ "q": 8 },
{ "q": 10 }
]
}$$::jsonb;
I'd like to map over the elements in the array and add an extra property to get something like this:
{
a: [
{ q: 5, r: [] },
{ q: 8, r: [] },
{ q: 10, r: [] }
]
}
Any and all would be greatly appreciated.
PostgreSQL 9.6.6
I think I worked it out. Something like:
SELECT my_column::jsonb ||
jsonb_build_object('a',
(SELECT json_agg(a::jsonb || jsonb_build_object('r','[]'::jsonb))
FROM jsonb_array_elements(my_column->'a') a)
)
FROM my_table;
Though this might not be perfect since I'm translating to this example without testing.
Rather than building a new object, you can use jsonb_set
SELECT jsonb_set(
mycol,
ARRAY['a'],
jsonb_agg(x || '{"r":[]}'::jsonb),
true
)
FROM foo
CROSS JOIN LATERAL jsonb_array_elements(mycol->'a')
AS t(x)
GROUP BY mycol;