sqlpostgresqljsonbjsonb-array-elements

Repeated values appear in array after updating jsonb column


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?


Solution

  • 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:

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