如何使用PostgreSQL更新JSON数组

Question

我有以下不便之处，我想只使用PostgreSQL更新JSON数组的键。我有以下json：

[
   {
      "ch":"1",
      "id":"12",
      "area":"0",
      "level":"Superficial",
      "width":"",
      "length":"",
      "othern":"5",
      "percent":"100",
      "location":" 2nd finger base"
   },
   {
      "ch":"1",
      "id":"13",
      "area":"0",
      "level":"Skin",
      "width":"",
      "length":"",
      "othern":"1",
      "percent":"100",
      "location":" Abdomen "
   }
]

如果“othern”=X，我需要将“othern”更新为另一个数字

（X是我传递给查询的任何数字。例如，如果othern=5，则更新othern）。

这个JSON可能要大得多，所以我需要一些可以在JSON数组中迭代的东西，找到所有与X数匹配的“othern”，并替换为新的。非常感谢。

我尝试了Postgresql的这些函数json，但我没有给出正确的结果：

    SELECT * FROM jsonb_to_recordset('[{"ch":"1", "id":"12", "area":"0", "level":"Superficial", "width":"", "length":"", "othern":"5", "percent":"100", "location":" 2nd finger base"}, {"ch":"1", "id":"13", "area":"0", "level":"Skin", "width":"", "length":"", "othern":"1", "percent":"100", "location":" Abdomen "}]'::jsonb) 
AS t (othern text);

我在SQL中发现了这个函数，它与我需要的类似，但老实说，SQL不是我的强项：

CREATE OR REPLACE FUNCTION "json_array_update_index"(
    "json"            json,
    "index_to_update" INTEGER,
    "value_to_update" anyelement
)
    RETURNS json
    LANGUAGE sql
    IMMUTABLE
    STRICT
AS $function$
SELECT concat('[', string_agg("element"::text, ','), ']')::json
FROM (SELECT CASE row_number() OVER () - 1
                 WHEN "index_to_update" THEN to_json("value_to_update")
                 ELSE "element"
                 END "element"
      FROM json_array_elements("json") AS "element") AS "elements"
$function$;


UPDATE plan_base
SET    atts = json_array_update_index([{"ch":"1", "id":"12", "area":"0", "level":"Superficial", "width":"", "length":"", "othern":"5", "percent":"100", "location":" 2nd finger base"}, {"ch":"1", "id":"13", "area":"0", "level":"Skin", "width":"", "length":"", "othern":"1", "percent":"100", "location":" Abdomen "}], '{"othern"}', '{"othern":"71"}'::json)
WHERE  id = 2;

Answer 1

您提供的函数更改JSON输入，给出更改后的JSON并更新一个并行表。

对于简单的更新，您不需要函数：

演示：db

UPDATE mytable
SET myjson = s.json_array
FROM (
    SELECT 
        jsonb_agg(
             CASE WHEN elems ->> 'othern' = '5' THEN
                 jsonb_set(elems, '{othern}', '"7"')
             ELSE elems  END
        ) as json_array
    FROM
        mytable,
        jsonb_array_elements(myjson) elems
) s

1. jsonb\u array\u elements（）将数组扩展为每个元素一行

如果您真的需要一个获取参数并返回更改后的JSON的函数，那么这可能是一个解决方案。当然，这不会执行更新。我不太确定您是否想实现这一点：

演示：db

CREATE OR REPLACE FUNCTION json_array_update_index(_myjson jsonb, _val_to_change int, _dest_val int)
RETURNS jsonb
AS $$
DECLARE
    _json_output jsonb;
BEGIN
    SELECT 
        jsonb_agg(
             CASE WHEN elems ->> 'othern' = _val_to_change::text THEN
                 jsonb_set(elems, '{othern}', _dest_val::text::jsonb)
             ELSE elems  END
        ) as json_array
    FROM
        jsonb_array_elements(_myjson) elems
    INTO _json_output;

    RETURN _json_output;
END;
$$ LANGUAGE 'plpgsql';

当然，如果你想像在问题中那样将两者结合起来，你可以这样做：

演示：db

CREATE OR REPLACE FUNCTION json_array_update_index(_myjson jsonb, _val_to_change int, _dest_val int)
RETURNS jsonb
AS $$
DECLARE
    _json_output jsonb;
BEGIN
    UPDATE mytable
    SET myjson = s.json_array
    FROM (
        SELECT 
            jsonb_agg(
                 CASE WHEN elems ->> 'othern' = '5' THEN
                     jsonb_set(elems, '{othern}', '"7"')
                 ELSE elems  END
            ) as json_array
        FROM
            mytable,
            jsonb_array_elements(myjson) elems
    ) s
    RETURNING myjson INTO _json_output;

    RETURN _json_output;
END;
$$ LANGUAGE 'plpgsql';