admin

带有jsonb_set()的UPDATE仅影响嵌套数组中的一个对象

sql

尝试更新jsonb列中嵌套数组的所有元素,但仅更新了一个元素。我的查询:

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;

查看带有样本值的演示:

db
<>在这里拨弄

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

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

但随后的不是:

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

阅读 215

收藏
2021-06-07

共1个答案

admin

解释

中再选择FROM您的从句UPDATE返回 _ 3 行。但是目标表中的每一行只能在单个命令中更新
_一次
UPDATE。结果是您只能看到这三行 之一 的效果。

或者,用手册的话来说:

使用时FROM,应确保该联接为要修改的每一行最多产生一个输出行。换句话说,目标行不应与其他表中的一行合并。如果是这样,那么将仅使用联接行之一来更新目标行,但是将很难预测将使用哪一行。

另外:不要将您的子查询称为“
cte”。这不是通用表表达式

恰当的 UPDATE

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<>[在这里](https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=06a3afecd8f9e42647779da1c924d4de)拨弄

使用jsonb_set()聚集它们放回阵列之前的每个对象(数组元素)上。首先在叶子级别,然后在更深层次。

我加idPRIMARY KEY表。我们需要一些唯一的列来使行分开。

添加的内容ORDER BY可能是必需的,也可能不是必需的。添加它以保证原始订单。

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

2021-06-07