1

I have json column with data that looks like this:

{
    a: [
        { q: 5 },
        { q: 8 },
        { q: 10 }
    ]
}

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. – Erwin Brandstetter Aug 19 '19 at 16:28
  • If it was my choice I would – mushishi78 Aug 19 '19 at 19:42
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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