1

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

2
  • If all your key names are stable, consider a relational design instead of the json column. Simpler queries, smaller, faster, cleaner. Aug 19, 2019 at 16:28
  • If it was my choice I would
    – mushishi78
    Aug 19, 2019 at 19:42

2 Answers 2

0

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.

1
  • You may find it faster to use jsonb_set Aug 9, 2021 at 2:13
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.