PostgreSQL中移除数组指定元素及空对象问题求助

你的UPDATE语句失效主要是这几个问题导致的：

• 用LIKE匹配JSON字符串太不靠谱，JSON键的顺序、空格稍有变化就匹配不到
• 反复替换布尔值再转JSONB的操作完全多余，PostgreSQL原生就能识别JSON里的true/false
• #-操作符是按固定路径删元素的，没法根据条件过滤数组里的特定元素
• 后面的JSON拼接逻辑混乱，生成的结构根本不符合预期

直接用下面的语句就能解决问题：

UPDATE clicks
SET protection_config = (
    WITH json_data AS (
        -- 先把字符串转成JSONB方便操作
        SELECT protection_config::jsonb AS data
        FROM clicks
        WHERE network_id = '5766'
          -- 精准匹配包含目标pixelId的记录，替代脆弱的LIKE
          AND data @> '{"pxg": {"trackingIds": [{"pixelId": "AW-23423524124"}]}}'
    ),
    filtered_tracking_ids AS (
        SELECT
            data,
            -- 展开数组，过滤掉目标pixelId的元素后重新聚合
            jsonb_agg(elem) AS new_tracking_ids
        FROM json_data,
             jsonb_array_elements(data->'pxg'->'trackingIds') AS elem
        WHERE elem->>'pixelId' != 'AW-23423524124'
        GROUP BY data
    )
    SELECT
        CASE
            -- 如果过滤后数组为空，直接删掉整个pxg对象
            WHEN new_tracking_ids = '[]'::jsonb THEN data - 'pxg'
            -- 否则更新pxg下的trackingIds数组
            ELSE jsonb_set(data, '{pxg, trackingIds}', new_tracking_ids)
        END::character varying
    FROM filtered_tracking_ids
)
WHERE network_id = '5766'
  AND protection_config::jsonb @> '{"pxg": {"trackingIds": [{"pixelId": "AW-23423524124"}]}}';

关键逻辑说明 #

1. 精准匹配目标记录：用@>操作符检查JSON是否包含指定结构，比LIKE可靠得多
2. 数组过滤与聚合：用jsonb_array_elements把trackingIds数组拆成单个元素，过滤掉目标pixelId后再用jsonb_agg重新拼成数组
3. 空数组处理：如果过滤后的数组是空的，就用data - 'pxg'删除整个pxg键
4. 类型转换：最后把处理好的JSONB转回character varying类型，和原列类型保持一致

测试结果 #

针对你提供的示例数据，执行后protection_config会变成：

{"monitoringMode":{"isMonitoring":false,"dates":null},"uaTrackingIds":[{"pixelId":"UA-123","dimensionIndex":"dimension123"},{"pixelId":"UA-1233","dimensionIndex":"dimension3"}]}

pxg对象被完全移除，符合预期。

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