Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE driver_orders (
  id SERIAL PRIMARY KEY
, driver_id INTEGER NOT NULL
, seats JSONB NOT NULL
);

INSERT INTO driver_orders VALUES
  (1, 10029, '[{"id":"#3","price":170000,"status":"OCCUPIED","user_id":10021},{"id":"#2","price":170000,"status":"AVAILABLE"}]')
, (2, 13098, '[{"id":"#4","price":170000,"status":"OCCUPIED","user_id":10021},{"id":"#1","price":200000,"status":"OCCUPIED","user_id":10021}]')
;

CREATE TABLE



INSERT 0 2


-- show
SELECT *
FROM   driver_orders
CROSS  JOIN LATERAL (
   SELECT jsonb_agg(CASE
                       WHEN elem->>'id' = '#2'
                       THEN jsonb_set(elem, '{status}', '"PICKED"', create_if_missing => false)
                       ELSE elem
                    END) AS seats_up
   FROM  jsonb_array_elements(seats) AS elem
   ) sub
WHERE  id = 1;


id
driver_id
seats
seats_up


1
10029
[{"id": "#3", "price": 170000, "status": "OCCUPIED", "user_id": 10021}, {"id": "#2", "price": 170000, "status": "AVAILABLE"}]
[{"id": "#3", "price": 170000, "status": "OCCUPIED", "user_id": 10021}, {"id": "#2", "price": 170000, "status": "PICKED"}]



SELECT 1


-- UPDATE
UPDATE driver_orders d
SET    seats =  (
   SELECT jsonb_agg(CASE
                       WHEN elem->>'id' = '#2'
                       THEN jsonb_set(elem, '{status}', '"PICKED"', create_if_missing => false)
                       ELSE elem
                    END)
   FROM   jsonb_array_elements(d.seats) AS elem
   )
WHERE  id = 1;

UPDATE 1


TABLE driver_orders;


id
driver_id
seats


2
13098
[{"id": "#4", "price": 170000, "status": "OCCUPIED", "user_id": 10021}, {"id": "#1", "price": 200000, "status": "OCCUPIED", "user_id": 10021}]

1
10029
[{"id": "#3", "price": 170000, "status": "OCCUPIED", "user_id": 10021}, {"id": "#2", "price": 170000, "status": "PICKED"}]



SELECT 2

id	driver_id	seats
2	13098	[{"id": "#4", "price": 170000, "status": "OCCUPIED", "user_id": 10021}, {"id": "#1", "price": 200000, "status": "OCCUPIED", "user_id": 10021}]
1	10029	[{"id": "#3", "price": 170000, "status": "OCCUPIED", "user_id": 10021}, {"id": "#2", "price": 170000, "status": "PICKED"}]