Your Location is: Home > Postgresql-9.5

Update array's items from x to y by one query

From: Venezuela View: 1416 a_subscriber 

Question

column data type = jsonb

I json has array availability with 20 items.

Example:

 "availability": [
    {
      "qty": 2,
      "price": 875,
     "is_available": false
 },
   {
      "qty": 21,
      "price": 1875,
     "is_available": false
 } 
...

I want to update array's items from index = 2 to 4.

I use this

UPDATE my_table
  SET data = jsonb_set(data, '{availability, 2, is_available}', 'true', true)
where (my_table.data #>> '{availability, 2, qty}')::numeric > 0;


UPDATE my_table
  SET data = jsonb_set(data, '{availability, 3, is_available}', 'true', true)
where (my_table.data #>> '{availability, 3, qty}')::numeric > 0;

UPDATE my_table
  SET data = jsonb_set(data, '{availability, 4, is_available}', 'true', true)
where (my_table.data #>> '{availability, 4, qty}')::numeric > 0;

I execute 3 queries. And it's work.

But... is it possible by ONE query to update 3 items in array(by index from 2 to 4) ?

Best answer