Update jsonb type column based on data from another table-CodePudding

I have a table orders with jsonb field eventlog and I have another table ids_tmp with two columns old_id and new_id. The jsonb eventlog field has attributes userId with values equal to old_id from ids_tmp within json array.

I have to change old_id'es to new_id'es in eventlog. I have this script, but i dont understand how to put result json to specific row in orders

UPDATE  orders g
SET     eventlog = s.json_array
FROM (
    SELECT 
        jsonb_agg(
             CASE WHEN exists (select 1 from ids_tmp h where cast(h.old_id as varchar) = elems ->> 'userId') THEN
                 jsonb_set(elems, '{userId}', ( select cast(cast(h.new_id as varchar) as jsonb) from ids_tmp h where cast(h.old_id as varchar) = elems ->> 'userId' ))
             ELSE elems  END
        ) as json_array
    FROM
        orders,
        jsonb_array_elements(eventlog) elems
) s

CodePudding user response：

Found a solution

update orders o 
set eventlog = ( 
    SELECT 
        jsonb_agg(
             CASE WHEN exists (select 1 from ids_tmp h where cast(h.old_idas varchar) = elems ->> 'userId') THEN
                 jsonb_set(elems, '{userId}', ( select cast(cast(h.new_id as varchar) as jsonb) from ids_tmp  h where cast(h.old_id as varchar) = elems ->> 'userId' ))
             ELSE elems  END
        ) as json_array
    FROM
        jsonb_array_elements(o.eventlog) elems  
)
where o.id in (
        select  d.id
        from    orders d, 
                jsonb_array_elements(eventlog) elems
        where   exists (select 1 from ids_tmp h where cast(h.old_id as varchar) = elems ->> 'userId')
    )
;

CodePudding user response：

You either need to join the orders rows from the FROM clause to the orders g that you're updating (by order id or something, in a WHERE statement):

UPDATE  orders g
SET     eventlog = s.json_array
FROM (
    SELECT
        o.id,
        jsonb_agg(
             CASE WHEN h.new_id IS NOT NULL
             THEN jsonb_set(elems, '{userId}', h.new_id)
             ELSE elems END
        ) as json_array
    FROM
        orders o,
        LATERAL jsonb_array_elements(o.eventlog) elems,
        LEFT JOIN ids_temp h ON h.old_id = (elems ->> 'userId')::int
    GROUP BY
        o.id
--  ^^^^^^^^ also necessary
) s
WHERE   g.id = s.id;
--      ^^^^^^^^^^^

or you should use a subquery referencing g in the SET clause instead of using a FROM:

UPDATE  orders g
SET     eventlog = (
    SELECT 
        jsonb_agg(
             CASE WHEN h.new_id IS NOT NULL
             THEN jsonb_set(elems, '{userId}', h.new_id)
             ELSE elems END
        )
    FROM
        jsonb_array_elements(g.eventlog) elems,
--                           ^
        LEFT JOIN ids_temp h ON h.old_id = (elems ->> 'userId')::int
)

Also I've taken the liberty to simplify that WHEN EXISTS(…) subquery into a left join.