Update multiple keys in json data in same query-CodePudding

I have the following query:

UPDATE events e
SET  
   details = jsonb_set ( details, '{"data", "user_name"}', '"Resident"' ),
   details = jsonb_set ( details, '{"data", "is_by_support"}', '"false"' ),
   details = jsonb_set ( details, '{"data", "is_by_resident"}', '"true"' ),
    updated_by = 1,
    updated_on = now ()
FROM
    test_req tr
WHERE
e.id = 12345 AND
    AND e.data_reference_id = tr.id
    AND e.event_type_id = 4
    AND e.created_by = 2
    AND e.updated_by = 2
    AND e.details -> 'data' ->> 'user_name' = 'Test'
    AND e.details -> 'data' ->> 'is_by_support' = 'true'
    AND e.details -> 'data' ->> 'is_by_resident' = 'false';

After executing it gives me an error as

ERROR:  multiple assignments to same column "details"

How to use multiple keys update in the same query? Is there any other way?

CodePudding user response：

You cannot have the same column several times in the SET clause of an UPDATE. Why not do something like

SET details = jsonb_set(
                 jsonb_set(
                    jsonb_set(
                       details,
                       '{"data", "user_name"}',
                       '"Resident"'
                    ),
                    '{"data", "is_by_support"}',
                    '"false"'
                 ),
                 '{"data", "is_by_resident"}',
                 '"true"'
              )

CodePudding user response：

try this :

WITH list AS
(  
SELECT e.id, jsonb_set(
                     jsonb_set(
                        jsonb_set(
                           details,
                           '{"data", "user_name"}',
                           '"Resident"'
                        ),
                        '{"data", "is_by_support"}',
                        '"false"'
                     ),
                     '{"data", "is_by_resident"}',
                     '"true"'
                  ) AS sol
FROM events e
INNER JOIN test_req tr
ON e.data_reference_id = tr.id
    WHERE e.id = 12345
        AND e.event_type_id = 4
        AND e.created_by = 2
        AND e.updated_by = 2
        AND e.details -> 'data' ->> 'user_name' = 'Test'
        AND e.details -> 'data' ->> 'is_by_support' = 'true'
        AND e.details -> 'data' ->> 'is_by_resident' = 'false'
)
UPDATE events e
SET details = l.sol,
    updated_by = 1,
    updated_on = now ()
    FROM list AS l
WHERE e.id = l.id