simplifying postgres queries

Question

Note: Asking to improve the working code.

My data column is as follows

{
   "color":"red",
   "toy":{
      "id":27,
      "name":"Truck",
      "price":12,
      "available":true
   },
   "quantity":"12"
}

In the above data, I want to set available to false and price to zero.

To do this I Am using the below code.

UPDATE toys 
SET data=JSONB_SET(data, '{toy,available}','false') 
WHERE data->'toy'->>'id'='27';

UPDATE toys 
SET data=JSONB_SET(data, '{toy,price}','0') 
WHERE data->'toy'->>'id'='27';

My question is it possible to update both values in a single query?

Thanks.

Laurenz Albe · Accepted Answer · 2021-12-01 12:49:36Z

1

Sure:

UPDATE toys
SET data = jsonb_set(
              jsonb_set(data, '{toy,available}', 'false'),
              '{toy,price}',
              '0'
           )
WHERE data->'toy'->>'id'='27';

answered Dec 1, 2021 at 12:49

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

Collectives™ on Stack Overflow

simplifying postgres queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related