使用 jsonb_set() 有条件更新

我在 Postgres 11.3 数据库中有一个表，其中包含jsonb column.

尝试更新嵌套数组名称内的所有对象"iProps".

如果路径{iProps -> value -> rules -> ao -> sc}是一个对象，那么路径应该从对象更新为具有值的字符串{iProps -> value -> rules -> ao -> sc -> name}

如果路径{iProps -> value -> rules -> ao -> sc}不存在，则该对象应保持不变。

使用查询测试设置：

期望的结果：

{
    "iProps": [
        {
            "value": {
                "rules": [
                    {
                        "ao": {
                            "set": "get"
                        },
                        "name": "PRule"
                    },
                    {
                        "ao": {
                            "sc":  "name1"
                            
                        }
                    },
                    {
                        "ao": {
                            "sc": "name2"
                            
                        }
                    },
                    {
                        "ao": {
                            "sc":  "name3"                            
                        }
                    }
                ]
            }
        }
    ]
}

我已经修改了查询并链接到小提琴中。有人可以看一下是否正确吗？

A plain CASE https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE应该有所作为。

UPDATE table_ t
SET    value_ = jsonb_set(value_, '{iProps}', sub2.new_prop, false)
FROM  (
   SELECT id
        , jsonb_agg(jsonb_set(prop, '{value, rules}', new_rules, false)
                    ORDER BY idx1) AS new_prop
   FROM  (
      SELECT t.id, arr1.prop, arr1.idx1
           , jsonb_agg(CASE WHEN jsonb_typeof(rule #> '{ao,sc}') = 'object'
                            THEN jsonb_set(rule, '{ao,sc}', rule #> '{ao,sc,name}', false)
                            ELSE rule
                       END
                       ORDER BY idx2) AS new_rules
      FROM table_ t
         , jsonb_array_elements(value_->'iProps')       WITH ORDINALITY arr1(prop,idx1)
         , jsonb_array_elements(prop->'value'->'rules') WITH ORDINALITY arr2(rule,idx2)
      GROUP  BY t.id, arr1.prop, arr1.idx1
      ) sub1
   GROUP  BY id
   ) sub2
WHERE t.id = sub2.id;

数据库小提琴（Postgres 11！）

为了还满足您在更新中添加的第二个过滤器（必须是object），检查jsonb_typeof() https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE.

查询在似乎不必要地复杂（tl；dr）。此外，它不保留数组元素的原始顺序。如果这实际上无关紧要，请省略WITH ORDINALITY and ORDER BY并进一步简化：

UPDATE table_ t
SET    value_ = jsonb_set(value_, '{iProps}', sub2.new_prop, false)
FROM  (
   SELECT id
        , jsonb_agg(jsonb_set(prop, '{value, rules}', new_rules, false)) AS new_prop
   FROM  (
      SELECT t.id, prop
           , jsonb_agg(CASE WHEN jsonb_typeof(rule #> '{ao,sc}') = 'object'
                            THEN jsonb_set(rule, '{ao,sc}', rule #> '{ao,sc,name}', false)
                            ELSE rule
                       END) AS new_rules
      FROM table_ t
         , jsonb_array_elements(value_->'iProps')       prop
         , jsonb_array_elements(prop->'value'->'rules') rule
      GROUP  BY t.id, prop
      ) sub1
   GROUP  BY id
   ) sub2
WHERE t.id = sub2.id;

数据库小提琴

这通常仍然保留数组元素的顺序（与原始数组不同）。两个级别的聚合并不能保证。

See:

• PostgreSQL unnest() 与元素编号 https://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number/8767450#8767450

我对您之前相关问题的回答中提供了更多建议：

• 使用 jsonb_set() 进行更新仅影响嵌套数组中的一个对象 https://stackoverflow.com/questions/65799161/update-with-jsonb-set-only-affects-one-object-in-nested-array/65800790#65800790