PostgreSQL 13 JSONB嵌套数组指定对象删除方法咨询

针对你要移除JSONB数组中包含{'id':138105}对象的需求，可通过两条独立的UPDATE语句分别处理topTenUsersBySwims和topTenUsersByCalories数组，假设你的表名为stats_table，JSONB字段名为data：

1. 移除topTenUsersBySwims数组中的目标对象 #

UPDATE stats_table
SET data = (
  SELECT jsonb_agg(
    CASE
      WHEN elem ? 'topTenUsersBySwims' THEN
        elem || jsonb_build_object(
          'topTenUsersBySwims',
          (SELECT jsonb_agg(user_obj)
           FROM jsonb_array_elements(elem->'topTenUsersBySwims') AS user_obj
           WHERE (user_obj->'id')::int != 138105)
        )
      ELSE elem
    END
  )
  FROM jsonb_array_elements(data) AS elem
);

2. 移除topTenUsersByCalories数组中的目标对象 #

UPDATE stats_table
SET data = (
  SELECT jsonb_agg(
    CASE
      WHEN elem ? 'topTenUsersByCalories' THEN
        elem || jsonb_build_object(
          'topTenUsersByCalories',
          (SELECT jsonb_agg(user_obj)
           FROM jsonb_array_elements(elem->'topTenUsersByCalories') AS user_obj
           WHERE (user_obj->'id')::int != 138105)
        )
      ELSE elem
    END
  )
  FROM jsonb_array_elements(data) AS elem
);

语句说明 #

• jsonb_array_elements(data)：将外层的JSONB数组拆分为单个元素，便于逐个处理每个月份的统计数据
• 内层的jsonb_array_elements(elem->'目标数组字段')：展开每个统计元素中的目标用户数组
• WHERE (user_obj->'id')::int != 138105：过滤掉id为138105的用户对象，用类型转换确保数字比较的准确性
• jsonb_agg(user_obj)：将过滤后的用户对象重新聚合成数组
• elem || jsonb_build_object(...)：将更新后的用户数组替换回原统计元素中的对应字段
• 外层的jsonb_agg(...)：将所有处理后的统计元素重新聚合成完整的JSONB数组，覆盖原字段值

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