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 @ stackoverflow.com/questions/18209625/… — Vivek
– Vivek, Commented May 6, 2019 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
– microman, Commented May 6, 2019 at 18:13
You can check my answer on this question: stackoverflow.com/questions/55071171/… — Dimitar Spasovski
– Dimitar Spasovski, Commented May 6, 2019 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
– microman, Commented May 6, 2019 at 18:30

Erwin Brandstetter · Accepted Answer · 2019-05-06 20:22:54Z

1

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:

edited May 6, 2019 at 20:22

answered May 6, 2019 at 19:27

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

microman Over a year ago

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?

Erwin Brandstetter Over a year ago

@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.

Collectives™ on Stack Overflow

How to update JSONB array based on property name?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related