基于类型 json 数组的动态输入更新 postgres 中的 JSON

Question

我在 postgres 表中有一个 JSON 类型的列，看起来像

{
    "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": ""
        }
    ]
}

我想更新 newInputItem 中提供的项目数量的日期， 这意味着中途岛航班和专家航班的日期需要更改。

我尝试使用 CTE，如下所示，但查询仅更新输入数组的第一个元素，在本例中，它只是更新的中途航班。

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;

Answer 1

为了在同一查询中对相同的 jsonb 数据进行多次更新，您需要基于标准 jsonb_set 函数创建一个聚合函数：

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) ;

然后，您就可以'如果不能直接在 UPDATE 语句的 SET 子句中调用聚合函数，则必须在 UPDATE 语句之前插入一个额外的 cte：

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 ;