Jsonb array of objects update-CodePudding

So this is my jsonb array of objects. Table is called bids in my db.

 [
      {
        "id": "1",
        "size": "5.5Y",
        "price": 180
      },
      {
        "id": "f0d1d36a-f6af-409e-968e-54c1dc104566",
        "size": "6.5Y",
        "price": 22
      }
    ]

I want to update price property by the ID of an element for ex. "f0d1d36a-f6af-409e-968e-54c1dc104566", so the price would change from 22 to for ex. 150. How can I do that?

CodePudding user response：

create table json_update (id integer, json_fld jsonb);
insert into json_update values (1, '[
      {
        "id": "1",
        "size": "5.5Y",
        "price": 180
      },
      {
        "id": "f0d1d36a-f6af-409e-968e-54c1dc104566",
        "size": "6.5Y",
        "price": 22
      }
    ]'
)
;


UPDATE
    json_update
SET
    json_fld = jsonb_set(json_fld, ARRAY[(idx)::text, 'price'::text], '150'::jsonb)
FROM (
    SELECT
        (row_number() OVER (ORDER BY t.a ->> 'id') - 1) AS idx,
        t.a
    FROM (
        SELECT
            jsonb_array_elements(json_fld)
        FROM
            json_update) AS t (a)) AS i
WHERE
    i.a ->> 'id' = 'f0d1d36a-f6af-409e-968e-54c1dc104566';

select * from json_update ;
 id |                                                         json_fld                                                          
---- ---------------------------------------------------------------------------------------------------------------------------
  1 | [{"id": "1", "size": "5.5Y", "price": 180}, {"id": "f0d1d36a-f6af-409e-968e-54c1dc104566", "size": "6.5Y", "price": 150}]