PostgreSQL JSONB列重复及相似键值移除更新方案问询

需求说明 #

需要清理JSONB列中的两类冗余键值对：

• 重复键：键名仅大小写不同（如INSURANCE和Insurance），保留首字母大写、其余小写的格式
• 相似键：键名语义重叠（如Physicians和PHYSICIANS & SURGEONS），可保留其中一个（示例中保留更长的PHYSICIANS & SURGEONS）

示例数据与预期结果 #

原始JSONB值：

{"Service Types": {"INSURANCE": true, "Insurance": true}}
{"Service Types": {"HOSPITALS": true, "Hospitals": true}}
{"Service Types": {"DENTISTS": true, "Dentists": true}}
{"Service Types": {"Physicians": true, "PHYSICIANS & SURGEONS": true}}

清理后预期结果：

{"Service Types": {"Insurance": true}}
{"Service Types": {"Hospitals": true}}
{"Service Types": {"Dentists": true}}
{"Service Types": {"PHYSICIANS & SURGEONS": true}}

实现方案 #

步骤1：创建清理函数 #

定义一个PL/pgSQL函数，处理单个JSONB对象的去重逻辑：

CREATE OR REPLACE FUNCTION clean_service_types(jsonb_data jsonb)
RETURNS jsonb AS $$
DECLARE
    cleaned jsonb := '{}'::jsonb;
    key text;
    lower_key text;
    similar_keys text[];
    selected_key text;
BEGIN
    -- 遍历"Service Types"下的所有键
    FOR key IN SELECT jsonb_object_keys(jsonb_data -> 'Service Types') LOOP
        lower_key := lower(key);
        
        -- 处理大小写重复的键：收集所有小写后匹配的键
        similar_keys := ARRAY(
            SELECT k FROM jsonb_object_keys(jsonb_data -> 'Service Types') k
            WHERE lower(k) = lower_key
        );
        
        -- 大小写重复时，选择首字母大写的标准格式
        IF array_length(similar_keys, 1) > 1 THEN
            selected_key := initcap(lower(key));
        ELSE
            -- 处理语义相似的键：判断键名是否存在包含关系
            similar_keys := ARRAY(
                SELECT k FROM jsonb_object_keys(jsonb_data -> 'Service Types') k
                WHERE (lower(k) LIKE '%' || lower_key || '%' OR lower(lower_key) LIKE '%' || lower(k) || '%')
                AND k != key
            );
            
            -- 存在相似键时，选择长度更长的键（可按需调整规则）
            IF array_length(similar_keys, 1) > 0 THEN
                selected_key := (SELECT k FROM (SELECT key AS k UNION SELECT unnest(similar_keys)) t ORDER BY length(k) DESC LIMIT 1);
            ELSE
                selected_key := key;
            END IF;
        END IF;
        
        -- 避免重复添加相同键
        IF NOT jsonb_exists(cleaned, selected_key) THEN
            cleaned := cleaned || jsonb_build_object(selected_key, (jsonb_data -> 'Service Types' -> key));
        END IF;
    END LOOP;
    
    -- 重新包装成"Service Types"结构返回
    RETURN jsonb_build_object('Service Types', cleaned);
END;
$$ LANGUAGE plpgsql;

步骤2：执行批量更新 #

假设表名为services，JSONB列名为service_data，执行以下语句完成更新：

UPDATE services
SET service_data = clean_service_types(service_data)
WHERE service_data ? 'Service Types'; -- 仅更新包含"Service Types"的行

逻辑说明 #

1. 大小写重复处理：通过lower()统一键名格式，用initcap()生成标准键名，确保仅保留一个合规键
2. 相似键处理：通过LIKE判断键名的包含关系，默认选择长度更长的键作为保留对象（可根据业务需求修改规则，比如保留更短的键）
3. 函数自动跳过已添加的键，避免重复写入

测试验证 #

可通过SELECT语句先验证函数效果：

SELECT clean_service_types('{"Service Types": {"INSURANCE": true, "Insurance": true}}'::jsonb);
-- 输出：{"Service Types": {"Insurance": true}}

SELECT clean_service_types('{"Service Types": {"Physicians": true, "PHYSICIANS & SURGEONS": true}}'::jsonb);
-- 输出：{"Service Types": {"PHYSICIANS & SURGEONS": true}}

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