PostgreSQL中CASE多类型场景下JSON保留原数据类型的实现方案

问题 #

我有一张包含employee_id、value_storage_type、value_date、value_decimal、value_string列的表，样例数据如下：

我编写了如下查询，按employee_id分组聚合生成JSON：

SELECT json_object_agg(regexp_replace(custom_fields_1.name::text, '[^a-zA-Z0-9]'::text, '_'::text, 'g'::text),
                CASE custom_fields.data_type
                    WHEN 'date'::text THEN fv.value_date::text::character varying
                    WHEN 'number'::text THEN fv.value_decimal::text::character varying
                    ELSE fv.value_string
                END)::jsonb AS db_json_data
           FROM abc.custom_fields
GROUP BY abc.employee_id

这个查询能正常运行，但decimal类型的数据被转成了字符串，生成的JSON示例如下：

{
    "Married": null,
    "TShirt_Size": "M",
    "Dependant_DOB": null,
    "Dependant_Sex": null,
    "Salary": "300.00000",
    "Dependant_Name_": "Joe Smith",
    "Beneficiary_Name": null,
    "Beneficiary_Percentage": null,
    "Single_Family_Coverage": "Active"
}

我期望decimal类型保持数值类型，得到这样的JSON：

{
    "Married": null,
    "TShirt_Size": "M",
    "Dependant_DOB": null,
    "Dependant_Sex": null,
    "Family_Members": 300.00000,
    "Dependant_Name_": "Joe Smith",
    "Beneficiary_Name": null,
    "Beneficiary_Percentage": null,
    "Single_Family_Coverage": "Active"
}

CASE语句不允许同一分支返回不同数据类型，请问该怎么实现保留原数据类型的需求？

解决方案 #

核心思路是先为不同类型的数据生成对应的JSON值，再进行聚合，这样就能避开CASE分支类型不一致的限制。以下是两种可行的实现方式：

方法一：使用to_jsonb统一转换类型 #

SELECT jsonb_object_agg(
    regexp_replace(cf.name::text, '[^a-zA-Z0-9]', '_', 'g'),
    CASE cf.data_type
        WHEN 'date' THEN to_jsonb(fv.value_date)
        WHEN 'number' THEN to_jsonb(fv.value_decimal)
        ELSE to_jsonb(fv.value_string)
    END
) AS db_json_data
FROM abc.custom_fields cf
JOIN abc.field_values fv ON cf.id = fv.custom_field_id -- 根据实际表结构补充关联条件
GROUP BY fv.employee_id;

方法二：直接将字段转为JSONB类型 #

SELECT jsonb_object_agg(key, value) AS db_json_data
FROM (
    SELECT 
        fv.employee_id,
        regexp_replace(cf.name::text, '[^a-zA-Z0-9]', '_', 'g') AS key,
        CASE cf.data_type
            WHEN 'date' THEN fv.value_date::jsonb
            WHEN 'number' THEN fv.value_decimal::jsonb
            ELSE fv.value_string::jsonb
        END AS value
    FROM abc.custom_fields cf
    JOIN abc.field_values fv ON cf.id = fv.custom_field_id -- 根据实际表结构补充关联条件
) AS sub
GROUP BY employee_id;

关键说明 #

1. to_jsonb()或直接转::jsonb会自动保留原数据类型：decimal会转为JSON数值类型，日期转为JSON日期类型，字符串保持字符串类型。
2. 原查询可能遗漏了字段定义表和实际值表的关联条件，需要根据你的真实表结构补充对应关联逻辑。
3. 使用jsonb_object_agg替代json_object_agg，在JSONB类型的处理上性能和兼容性更优。

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