PostgreSQL按键值移除JSONB对象及字段的技术求助

针对你的ClickTrueTags表中character varying类型的protection_config字段，以下是满足两个需求的具体SQL实现：

需求1：移除trackingIds中指定pixelId的对象 #

由于字段是字符串类型，需要先转换为jsonb（PostgreSQL中更适合操作的JSON类型），过滤掉pixelId = 'AW-12314555'的元素后，再更新回原字段。

查询验证语句（先确认结果） #

SELECT 
  jsonb_set(
    protection_config::jsonb,
    '{pxg, trackingIds}',
    (
      SELECT jsonb_agg(elem)
      FROM jsonb_array_elements(protection_config::jsonb->'pxg'->'trackingIds') elem
      WHERE elem->>'pixelId' != 'AW-12314555'
    )
  ) AS updated_protection_config
FROM ClickTrueTags;

实际更新语句 #

UPDATE ClickTrueTags
SET protection_config = jsonb_set(
    protection_config::jsonb,
    '{pxg, trackingIds}',
    (
      SELECT jsonb_agg(elem)
      FROM jsonb_array_elements(protection_config::jsonb->'pxg'->'trackingIds') elem
      WHERE elem->>'pixelId' != 'AW-12314555'
    )
  )::character varying;

说明：

• protection_config::jsonb：将字符串类型转换为jsonb以便操作
• jsonb_array_elements：展开trackingIds数组为行数据
• jsonb_agg：将过滤后的元素重新聚合为数组
• jsonb_set：把新的数组替换回原JSON结构的对应路径
• 最后转换回character varying类型存回字段

需求2：完全移除pxg字段 #

直接使用-操作符删除指定键，再转换回原类型。

查询验证语句 #

SELECT (protection_config::jsonb - 'pxg') AS updated_protection_config
FROM ClickTrueTags;

实际更新语句 #

UPDATE ClickTrueTags
SET protection_config = (protection_config::jsonb - 'pxg')::character varying;

说明：

• protection_config::jsonb - 'pxg'：直接删除jsonb对象中的pxg键
• 转换回character varying类型完成更新

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