带有jsonb_set()的UPDATE仅影响嵌套数组中的一个对象 [英] UPDATE with jsonb_set() only affects one object in nested array

问题描述

试图更新jsonb列中嵌套数组的所有元素，但仅更新一个元素.我的查询:

Trying to update all elements of a nested array in a jsonb column, but only one element is updated. My query:

update table_ 
 set value_ = jsonb_set(value_,cte.json_path,cte.namevalue,false) FROM (
select 
 vals2->'ao'->'sc'->'name' as namevalue,
  ('{iProps,'||index1-1||',value,rules,'||index2-1||',ao,sc}')::text[] as json_path
from 
  table_, 
  jsonb_array_elements(value_->'iProps') 
  with ordinality arr1(vals1,index1),
  jsonb_array_elements(vals1->'value'->'rules') 
  with ordinality arr2(vals2,index2)
  ) AS cte;

查看带有示例值的演示:

See demo with sample value:

db<>小提琴此处

我无法理解为什么此查询更新rules数组中的第一个对象:

Am unable to understand why this query updates the first object in the rules array:

iProps -> value -> rules -> ao -> sc -> name = "name1"

但随后的没有:

iProps -> value -> rules -> ao -> sc -> name = "name2"
iProps -> value -> rules -> ao -> sc -> name = "name3" 

推荐答案

说明

UPDATE的FROM子句中的子选择将返回 三 行.但是目标表中的每一行只能在单个UPDATE命令中一次进行更新.结果是您只看到这三行中的 的效果.

Explanation

The subselect in the FROM clause of your UPDATE returns three rows. But every row in the target table can only be updated once in a single UPDATE command. The result is that you only see the effect of one of those three rows.

或者用手册:

使用FROM时，应确保联接最多产生一个 每行要修改的输出行.换句话说，目标行 不应连接到其他表中的多个行.如果它 这样做，那么只有连接行之一将用于更新目标 行，但是使用哪一个并不容易预测.

When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

此外:请勿将您的子查询称为"cte".它不是公用表表达式.

Aside: don't call your subquery "cte". It's not a Common Table Expression.

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(jsonb_set(rule, '{ao,sc}', rule #> '{ao,sc,name}', false)
                       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;

db<>小提琴此处

在每个对象(数组元素)上使用jsonb_set()，然后再将它们聚合回数组中.首先是在叶子级别，然后在更深层次.

Use jsonb_set() on each object (array element) before aggregating them back into an array. First at the leaf level, and again on the deeper level.

我在表中添加了id作为PRIMARY KEY.我们需要一些唯一的列来使行分开.

I added id as PRIMARY KEY to the table. We need some unique column to keep rows separate.

添加的ORDER BY可能是必需的，也可能不是必需的.添加它以保证原始订单.

The added ORDER BY may or may not be required. Added it to guarantee original order.

当然，如果您的数据与样本一样规则，则具有专用列的关系设计可能是更简单的选择.见

Of course, if your data is as regular as the sample, a relational design with dedicated columns might be a simpler alternative. See

• 查看全文