postgresql - 如何更新 postgres 数组中的 json？

Question

我附上了 JSON 输入和输出

输入

{
  "cap1": [
    {
      "fe1": [
        {
          "par1": 0,
          "fet1": 6,
          "fun1": [
            {
              "fnd1": [
                {
                  "name": "v1",
                  "site": [
                    "w1",
                    "mb1",
                    "tb1"
                  ]
                }
              ]
            }
          ]
        }
      ],
      "capab1": 2
    },
    {
      "fe1": [
        {
          "par1": 0,
          "fet1": 42,
          "fun1": null
        },
        {
          "par1": 42,
          "fet1": 43,
          "fun1": null
        }
      ],
      "capab1": 11
    }
  ]
}

我想添加{"par1": 0, "fet1": 44, "fun1": null},{"par1": 0, "fet1": 45, "fun1": null}where "capab1": 11。

输出应该是

{
  "cap1": [
    {
      "fe1": [
        {
          "par1": 0,
          "fet1": 6,
          "fun1": [
            {
              "fnd1": [
                {
                  "name": "v1",
                  "site": [
                    "w1",
                    "mb1",
                    "tb1"
                  ]
                }
              ]
            }
          ]
        }
      ],
      "capab1": 2
    },
    {
      "fe1": [
        {
          "par1": 0,
          "fet1": 42,
          "fun1": null
        },
        {
          "par1": 42,
          "fet1": 43,
          "fun1": null
        },
        {
          "par1": 0,
          "fet1": 44,
          "fun1": null
        },
        {
          "par1": 0,
          "fet1": 45,
          "fun1": null
        }
      ],
      "capab1": 11
    }
  ]
}

标签： postgresqljsonbpostgresql-9.5

Answer 1

它有点复杂。您可以使用jsonb_array_elements取消嵌套数组并获取要更新的路径。JSONB_INSERT然后使用如下更新 JSON ：这是解决方案：

with cte as (
select  *,
('{cap1,'||index1-1||',fe1,0}')::text[] as json_path
from test,
jsonb_array_elements(col->'cap1') with ordinality arr1 (vals1,index1)
where (vals1->>'capab1')::int=11
)
 update test 
 set col = jsonb_insert(test.col,cte.json_path,'[{"par1": 0, "fet1": 44, "fun1": null},{"par1": 0, "fet1": 45, "fun1": null}]'::jsonb,true) 
 from cte 
 where test.id=cte.id

演示