Updating JSON in postgres based on dynamic input of type json array

Question

I have a column in postgres table which is of JSON type and looks something like

{
    "Name": "Some Name",
    "Stages": [
        {
          "Title": "Early Flight",
          "Tags": [....],
          "Date": "2021-11-05T00:00:00",
          "CloseDate": ""   
        },
        {
          "Title": "Midway Flight",
          "Tags": [....],
          "Date": "2021-11-05T00:00:00",
          "CloseDate": ""
        },
        {
          "Title": "Pro Flight",
          "Tags": [....],
          "Date": "2021-11-05T00:00:00",
          "CloseDate": ""
        },
        {
          "Title": "Expert Start",
          "Tags": [....],
          "Date": "2021-11-05T00:00:00",
          "CloseDate": ""
        }
    ]
}

I want to update the Date for the number of items that are provide in the newInputItem, meaning the Date for Midway Flight and Expert Flight needs to change.

I tried using CTE as below but the query updates only the first element of the input array in this case its just Midway Flight that gets updated.

WITH newInputItem as 
(
   select
      arr.newInputItem ::json ->> 'Title' as State,
      (arr.newInputItem ::json ->> 'NewDate')::timestamp as NewDate 
   from
      json_array_elements('[
       {"Title" : "Midway Flight", "Date" : "01 / 01 / 1777"},
       {"Title" : "Expert Flight", "Date" : "01 / 01 / 1999"} 
      ]') WITH ORDINALITY arr(newInputItem, index)
 ),
      oldItem AS 
      (
         SELECT
('{Stages,' || index - 1 || ',"Date"}')::TEXT[] AS path,
            user_id,
            arr.oldItem ::json ->> 'Title' AS title 
         FROM
        department.Process_Instance
            jsonb_array_elements(process_instance_data -> 'Stages') WITH ORDINALITY arr(oldItem, index) 
         WHERE
             department.Process_Instance."user_id" = 17 
      )

       UPDATE
           department.Process_Instance pi
       SET
          process_instance_data = jsonb_set(process_instance_data, oldItem.path, to_json(newInputItem.NewDate)::JSONB) 
      FROM    
         oldItem,
         newInputItem
      WHERE
         pi.user_id = oldItem.user_id 
         AND oldItem.title = newInputItem.State;

This would be so much easier with a properly normalized data model — , Mar 03 '22 at 07:13
Try this, [Using jsonb_set() for updating specific jsonb array value](https://stackoverflow.com/questions/38996212/using-jsonb-set-for-updating-specific-jsonb-array-value) — vikas95prasad, Mar 03 '22 at 07:22

score 1 · Accepted Answer · answered Mar 03 '22 at 18:55

In order to make several updates into the same jsonb data within the same query, you need to create an aggregate function based on the standard 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 AGGREGATE jsonb_set_agg(jsonb, text[], jsonb, boolean)
( sfunc = jsonb_set, stype = jsonb) ;

Then, as you can't call an aggregate function directly in the SET clause of an UPDATE statement, you have to insert an additional cte before your UPDATE statement :

WITH newInputItem as 
(
   select
      arr.newInputItem ::json ->> 'Title' as State,
      (arr.newInputItem ::json ->> 'NewDate')::timestamp as NewDate 
   from
      json_array_elements('[
       {"Title" : "Midway Flight", "Date" : "01 / 01 / 1777"},
       {"Title" : "Expert Flight", "Date" : "01 / 01 / 1999"} 
      ]') WITH ORDINALITY arr(newInputItem, index)
 ), oldItem AS 
(
   SELECT
      ('{Stages,' || index - 1 || ',"Date"}')::TEXT[] AS path,
      user_id,
      arr.oldItem ::json ->> 'Title' AS title 
   FROM
      department.Process_Instance
      jsonb_array_elements(process_instance_data -> 'Stages') WITH ORDINALITY arr(oldItem, index) 
   WHERE
      department.Process_Instance."user_id" = 17 
), final AS
(
   SELECT oldItem.user_id
        , jsonb_set_agg( process_instance_data, oldItem.path, 
                         to_json(newInputItem.NewDate)::JSONB, True) AS data_final
      FROM oldItem        
      INNER JOIN newInputItem
      ON oldItem.title = newInputItem.State
      GROUP BY oldItem.user_id
)
       UPDATE
           department.Process_Instance pi
       SET
          process_instance_data = final.data_final 
      FROM    
         final
      WHERE
         pi.user_id = final.user_id ;

I just had to include ```process_instance_data``` to the select query in ```oldItem``` — rax, Mar 04 '22 at 15:57

Updating JSON in postgres based on dynamic input of type json array

1 Answers1