Postgres - updating an array element in a json column

Question

I have a json column in a postgres table. The column contains the following json data:

{
    "data": {
        "id": "1234",
        "sites": [
            {
                "site": {
                    "code": "1",
                    "display": "Site1"
                }
            },
            {
                "site": {
                    "code": "2",
                    "display": "Site2"
                },
                "externalSite": true
            },
            {
                "site": {
                    "code": "3",
                    "display": "Site3"
                }
            }
        ]
    }
}

I need to create an update query that adds another attribute ('newAttribute' in the sample below) to all array items that have '"externalSite": true', so, after running the update query the second array element will be:

{
    "site": {
        "code": "2",
        "display": "Site2"
    },
    "externalSite": true,
    "newAttribute": true
}

The following query returns the array elements that need to be updated: select * from myTable, jsonb_array_elements(data -> 'sites') sites where sites ->'externalSite' = 'true'

What is the syntax of the update query?

Thanks Kobi

which one your like nodejs or someother technology? if nodejs what kind of npm using like sequlize or any other? — RGKrish183
I need it to be a postgresql query. This is a one time fix that should run on existing db and add missing attributes. — Kobi J

Jeremy Jeremy · Accepted Answer · 2020-01-06T13:40:04

Assuming your table is called test and your column is called data, you can update it like so:

UPDATE test SET data = 
  (select jsonb_set(data::jsonb, '{"data","sites"}', sites)
  FROM test
  CROSS JOIN LATERAL (
      SELECT jsonb_agg(CASE WHEN site ? 'externalSite' THEN site || '{"newAttribute":"true"}'::jsonb
                  ELSE site
             END) AS sites
      FROM jsonb_array_elements( (data#>'{"data","sites"}')::jsonb ) as ja(site)
  ) as sub
);

Note that I cast the data to jsonb data as there are more functions and operators available for manipulating jsonb than plain json.

You can run the SELECT statement alone to see what it is doing, but the basic idea is to re-create the sites object by expanding it with jsonb_array_elements and adding the newAttribute attribute if externalSite exists.

This array is then aggregated with jsonb_agg and, finally, in the outer select, the sites object is replaced entirely with this newly computed version.

Postgres - updating an array element in a json column

1 Answers