Skip to content
Advertisement

simplifying postgres queries

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.

Advertisement

Answer

Sure:

UPDATE toys
SET data = jsonb_set(
              jsonb_set(data, '{toy,available}', 'false'),
              '{toy,price}',
              '0'
           )
WHERE data->'toy'->>'id'='27';
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement