JSONb update multiply fields with one query

Question

I need to update data in two elements of JSON array. The example below is simplified, usually it has more elements wit other keys

[{"key": "startDate", "value": "2022-02-05T23:00:00Z"}, {"key": "endDate", "value": "2022-02-05T23:00:00Z"}]

My goal is to change 'value' in startDate and endDate

My query is

UPDATE my_table ti
SET fields = jsonb_set(ti.fields, path, temp, false)
FROM my_table ti1,
     LATERAL (
         SELECT ARRAY [(ordinal - 1)::text, 'value']                                               AS path,
                to_jsonb(((field ->> 'value')::timestamp with time zone at time zone 'CET')::date) AS temp
         FROM jsonb_array_elements(ti1.fields #> '{}') WITH ORDINALITY arr(field, ordinal)
         WHERE field ->> 'key' = 'startDate'
           AND field ->> 'value' IS NOT NULL
         ) field
WHERE ti.id = ti1.id;

And the same query I do for endDate, just replacing WHERE condition. So, I got two queries, but I want to replace it with one

I tried to rewrite WHERE condition to WHERE field ->> 'key' in ('startDate', 'endDate), but it didn't work and as a result I got only one (the first) key value updated

[{"key": "startDate", "value": "2022-02-06"}, {"key": "endDate", "value": "2022-02-05T23:00:00Z"}]

How can I update two values in one query?

user_8060017 · Answer

Solution 1

You need to call the jsonb_set function twice if you want to make two updates in the same jsonb data :

UPDATE my_table ti
   SET fields = jsonb_set(jsonb_set(ti1.fields, e.path, e.temp, false), s.path, s.temp, false)
  FROM my_table ti1
 CROSS JOIN LATERAL
     ( SELECT ARRAY [(arr.ordinal - 1)::text, 'value'] AS path
            , to_jsonb(((arr.field ->> 'value')::timestamp with time zone at time zone 'CET')::date) AS temp
         FROM jsonb_array_elements(ti1.fields) WITH ORDINALITY AS arr(field, ordinal)
        WHERE arr.field ->> 'key' = 'startDate'
          AND arr.field ->> 'value' IS NOT NULL
     ) AS s
 CROSS JOIN LATERAL
     ( SELECT ARRAY [(arr.ordinal - 1)::text, 'value'] AS path
            , to_jsonb(((arr.field ->> 'value')::timestamp with time zone at time zone 'CET')::date) AS temp
         FROM jsonb_array_elements(ti1.fields) WITH ORDINALITY AS arr(field, ordinal)
        WHERE arr.field ->> 'key' = 'endDate'
          AND arr.field ->> 'value' IS NOT NULL
     ) AS e
 WHERE ti.id = ti1.id

Solution 2

You can create an aggregate function jsonb_set_agg based on the jsonb_set function :

CREATE OR REPLACE FUNCTION jsonb_set(x jsonb, y jsonb, p text[], z jsonb, b boolean)
RETURNS jsonb LANGUAGE sql IMMUTABLE AS
$$ SELECT jsonb_set(COALESCE(x, y), p, z, b) $$ ;

CREATE OR REPLACE AGGREGATE jsonb_set_agg (jsonb, text[], jsonb, boolean)
( SFUNC = jsonb_set
, STYPE = jsonb
) ;

Then you can iterate on the new aggregate function jsonb_set_agg :

UPDATE my_table AS ti
   SET fields = r.res
  FROM my_table AS ti1
 CROSS JOIN LATERAL
     ( SELECT jsonb_set_agg( ti1.fields
                           , ARRAY [(arr.ordinal - 1)::text, 'value']
                           , to_jsonb(((arr.field ->> 'value')::timestamp with time zone at time zone 'CET')::date)
                           , false
                           ) AS res
         FROM jsonb_array_elements(ti1.fields) WITH ORDINALITY AS arr(field, ordinal)
        WHERE arr.field ->> 'key' IN ('startDate', 'endDate')
          AND arr.field ->> 'value' IS NOT NULL
     ) AS r
 WHERE ti.id = ti1.id

JSONb update multiply fields with one query

1 Answers