Repeated values appear in array after updating jsonb column

Question

I have this table:

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

I store this value in that table:

id	driver_id	seats
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}]

I am trying to update status field of seats array

I am following the accepted answer for this question: Update specific object in array of objects Postgres jsonb

Here is my version of the suggested query:

UPDATE driver_orders
SET seats = s.new_seats
FROM (
    SELECT 
        jsonb_agg(
            jsonb_build_object(
                'id', elem -> 'id',
                'price', elem -> 'price',
                'user_id', elem -> 'user_id',
                'status', CASE 
                             WHEN elem ->> 'id' = '#2' THEN 
                                '"PICKED"'
                             ELSE 
                                elem -> 'status' END
            )
        ) as new_seats
    FROM driver_orders,
        jsonb_array_elements(seats) as elem
) s
WHERE id = 1

I expect this query to set the status field of the seat with id='#2' to "PICKED". Instead seats array becomes the following:

[
    {
        "id": "#3",
        "price": 170000,
        "status": "OCCUPIED",
        "user_id": 10021
    },
    {
        "id": "#4",
        "price": 170000,
        "status": "OCCUPIED",
        "user_id": 10021
    },
    {
        "id": "#1",
        "price": 200000,
        "status": "OCCUPIED",
        "user_id": 10021
    },
    {
        "id": "#2",
        "price": 170000,
        "status": "PICKED",
        "user_id": 10021
    },
    {
        "id": "#4",
        "price": 140000,
        "status": "OCCUPIED",
        "user_id": 10021
    },
    {
        "id": "#1",
        "price": 160000,
        "status": "AVAILABLE",
        "user_id": null
    },
    ...  44 objects overall
]

Can someone point what I am doing wrong?

Answer 1

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;

fiddle

See:

• How to update JSONB array based on property name?

Would be much simpler with a separate table for seats instead of the jsonb column.