如何使用SQL解析字符串型键值对并过滤值为0的条目？

针对你的需求，以下是不同主流SQL数据库的实现方案：

PostgreSQL #

PostgreSQL的JSON处理能力较强，可直接转换并处理：

SELECT 
    Col1,
    CASE 
        WHEN filtered.count = 0 THEN '{}'
        ELSE '{' || string_agg(concat(key, ':', value), ', ') || '}'
    END AS Col2
FROM my_table
LEFT JOIN LATERAL (
    SELECT 
        key, 
        value,
        COUNT(*) OVER() AS count
    FROM jsonb_each(replace(replace(Col2, '{', '{"'), ':', '":')::jsonb)
    WHERE value::int != 0
) filtered ON true
GROUP BY Col1, filtered.count;

步骤说明：

• 通过replace将非标准JSON字符串转换为标准格式（给键添加双引号）
• 用jsonb_each展开键值对
• 过滤掉值为0的条目
• 用string_agg重新拼接符合要求的键值对，空结果返回{}

MySQL #

MySQL需要先将字符串转为标准JSON，再通过表函数展开处理：

SELECT 
    t.Col1,
    IFNULL(
        CONCAT('{', GROUP_CONCAT(CONCAT(j.key, ':', j.value) SEPARATOR ', '), '}'),
        '{}'
    ) AS Col2
FROM my_table t
LEFT JOIN JSON_TABLE(
    REPLACE(REPLACE(t.Col2, '{', '{"'), ':', '":'),
    '$.*' COLUMNS(
        key VARCHAR(50) PATH '$',
        value VARCHAR(50) PATH '$'
    )
) j ON CAST(j.value AS UNSIGNED) != 0
GROUP BY t.Col1;

步骤说明：

• 先转换为标准JSON格式
• 用JSON_TABLE将JSON对象拆分为行
• 过滤值不为0的行
• 用GROUP_CONCAT拼接结果，空值返回{}

SQL Server #

SQL Server可通过OPENJSON处理，结合字符串拼接：

SELECT 
    Col1,
    CASE 
        WHEN COUNT(j.[key]) = 0 THEN '{}'
        ELSE '{' + STRING_AGG(CONCAT(j.[key], ':', j.value), ', ') + '}'
    END AS Col2
FROM my_table t
LEFT JOIN OPENJSON(REPLACE(REPLACE(t.Col2, '{', '{"'), ':', '":')) j
    ON CAST(j.value AS INT) != 0
GROUP BY Col1;

步骤说明：

• 转换字符串为标准JSON
• 用OPENJSON展开键值对
• 过滤值不为0的条目
• 用STRING_AGG拼接结果，无符合条件条目时返回{}

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