How to update array inside postgresql jsonb field?

Question

I have a table with the jsonb field, an example of value:

{
    "id": "test",
    "data": [
        {
            "folder1": ["id1", "id2", "id3"]
        }
    ]
}

I want to replace array ["id1", "id2", "id3"] inside this the data to [{"id": "id1", "size": 10}, {"id": "id2", "size": 100}, {"id": "id3", "size": 1000}]:

{
    "id": "test",
    "data": [
        {
            "folder1": [
                {"id": "id1", "size": 10}, 
                {"id": "id2", "size": 100}, 
                {"id": "id3", "size": 1000}
            ]
        }
    ]
}

If it possible to update it in the single sql construction ot I need to do it with pl/sql?

Having an array of object where each object has a unique identifier is an anti-pattern. If each object has in identify, use that identify as the key if a higher level object, rather using a higher level array. — jjanes

GMB GMB · Accepted Answer · 2020-02-27T13:36:03

If the path of the element to replace is fixed, you can use jsonb_set():

jsonb_set(
        mycol,
        '{data,0,folder1}',
         '[{"id": "id1", "size": 10}, {"id": "id2", "size": 100}, {"id": "id3", "size": 1000}]'::jsonb
    )

Demo on DB Fiddle - using jsonb_pretty() for formating.

How to update array inside postgresql jsonb field?

1 Answers