如何使用UPDATE语句更新PostgreSQL JSONB列中的嵌套数组

你的原SQL语句无法生效，核心问题是没有正确遍历嵌套的artList和names数组，过滤条件也指向了错误的字段。以下是针对需求的正确解法：

正确更新SQL #

UPDATE usr
SET art = (
  -- 重构最外层的art数组
  SELECT jsonb_agg(
    CASE WHEN elem->>'name' = 'Arjun' THEN
      -- 更新Arjun条目下的artList
      jsonb_set(elem, '{artList}', (
        SELECT jsonb_agg(
          CASE WHEN art_elem->>'category' = 'Sports' THEN
            -- 更新Sports分类下的names数组，过滤掉Cricket
            jsonb_set(art_elem, '{names}', (
              SELECT jsonb_agg(name_elem)
              FROM jsonb_array_elements(art_elem->'names') name_elem
              WHERE name_elem->>'label' <> 'Cricket'
            ))
          ELSE
            -- 其他分类保持原样
            art_elem
          END
        )
        FROM jsonb_array_elements(elem->'artList') art_elem
      ))
    ELSE
      -- 其他条目保持原样
      elem
    END
  )
  FROM jsonb_array_elements(usr.art) elem
)
-- 只更新包含Arjun条目的行，避免无意义更新
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(usr.art) elem
  WHERE elem->>'name' = 'Arjun'
);

原SQL的问题分析 #

1. 过滤条件错误：原查询中p.name = 'Sports'完全不符合需求，应该是针对artList元素的category字段进行过滤
2. 未处理嵌套层级：仅遍历了最外层的art数组，没有深入到artList和names两层嵌套数组，无法定位到要删除的目标元素
3. 关联逻辑混乱：子查询没有正确关联原表的行，导致生成的json_array无法匹配到正确的更新对象

内容的提问来源于stack exchange，提问作者Jharokha