How to update JSONB array based on property name?

Question

I would like to update fields of the following JSON array (stored in a column with JSONB datatype) based on the objectId.

        [
            {
                objectId: 'gDKn1jM5d',
                objectType: 'type1',
                posX: 50,
                posY: 100,
            },
            {
                objectId: '4dg5E8BDv',
                objectType: 'type2',
                posX: 50,
                posY: 100,
            },
            {
                objectId: 'ZmCwOf5N2',
                objectType: 'type3',
                posX: 100,
                posY: 150,
            }
        ]

In Mongodb I can use a simple update statement but I was not able to find a way in postgres.

For example I would like to update all array elements with objectId 'ZmCwOf5N2' to the posX value 300 (that means it would only affect the 3rd array item).

I'm looking for a plain SQL statement in order to execute the update. The postgres version is 11.

It is not possible for me to install extensions because I'm using a database as a service provider. However, in case there is no easy way to accomplish the update statement, I would be able to add a postgres function using e.g. C code.

The clear answer has posted @ https://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype — Vivek, May 06 '19 at 18:01
I've seen that but it does not describe how to update an array item by property value but instead only by array index. — microman, May 06 '19 at 18:13
You can check my answer on this question: https://stackoverflow.com/questions/55071171/postgresql-set-field-of-json-object-in-json-array/55080030#55080030 — Dimitar Spasovski, May 06 '19 at 18:26
I've seen that too. Do you know if that's the only solution? If so, it means that conditional (based on property) array manipulation is not yet supported I guess. — microman, May 06 '19 at 18:30

Erwin Brandstetter · Accepted Answer · 2019-05-06T20:22:54.140

UPDATE tbl t
SET    js = 
   (
   SELECT jsonb_agg(CASE WHEN elem->>'objectId' = 'ZmCwOf5N2'
                         THEN jsonb_set(elem, '{posX}', to_jsonb(int '300'))
                         ELSE elem
                    END) AS  js1 
   FROM   jsonb_array_elements(t.js) elem
   )
WHERE  t.js @> '[{"objectId": "ZmCwOf5N2"}]';

Note that this ..

adds the 'posX' key if it's missing
updates rows even where nothing changes

To only update existing keys and only update the row if the update actually changes the value:

UPDATE tbl t
SET    js = 
   (
   SELECT jsonb_agg(CASE WHEN elem->>'objectId' = 'ZmCwOf5N2'
                         THEN jsonb_set(elem, '{posX}', to_jsonb(int '300'), false)  -- !
                         ELSE elem
                    END) AS  js1 
   FROM   jsonb_array_elements(t.js) elem
   )
WHERE  t.js @> '[{"objectId": "ZmCwOf5N2"}]'
AND    js <>
   (
   SELECT jsonb_agg(CASE WHEN elem->>'objectId' = 'ZmCwOf5N2'
                         THEN jsonb_set(elem, '{posX}', to_jsonb(int '300'), false)
                         ELSE elem
                    END) AS  js1 
   FROM   jsonb_array_elements(t.js) elem
   );  --!

See:

This looks like a very good solution. Thanks a lot! Do you know if a native solution (a solution that is targeted specifically for that use case so it can be done in less code) is planned? — microman, May 06 '19 at 20:19
@microman: This is currently (Postgres 11) the shortest and fastest way. (Also note the variant I added!) Adding parts of the SQL/JSON path language is planned for Postgres 12. Not sure if this will be covered. — Erwin Brandstetter, May 06 '19 at 20:25

How to update JSONB array based on property name?

1 Answers1