PostgreSQL 14中如何将指定记录转换为目标JSON格式？

原始数据 #

select t.name_field, t.value_field 
from 
(
    values 
        ('name_1', 'val_fld_1'), 
        ('name_2', null), 
        ('name_3', 'val_fld__3')
) as t(name_field,value_field);

目标JSON格式 #

{"name_1" : [{"value" : "val_fld_1", "seq" : 1}], "name_2" : [{"value" : "", "seq" : 1}], "name_3" : [{"value" : "val_fld__3", "seq" : 1}]}

尝试的SQL（未达预期） #

select array_agg(json_build_object(t.name_field, json_build_array(json_build_object('value',  t.value_field)))) as my_test
from 
(
    values 
        ('name_1', 'val_fld_1'), 
        ('name_2', null), 
        ('name_3', 'val_fld__3')
) as t(name_field,value_field);

正确实现方案 #

你之前的问题在于用了array_agg，它会把每个键值对包装成数组元素，而我们需要的是单个JSON对象，应该用json_object_agg来聚合键值对。另外需要处理null值转为空字符串，同时固定seq为1。

正确的SQL如下：

select json_object_agg(
    t.name_field,
    json_build_array(
        json_build_object(
            'value', coalesce(t.value_field, ''),
            'seq', 1
        )
    )
) as target_json
from 
(
    values 
        ('name_1', 'val_fld_1'), 
        ('name_2', null), 
        ('name_3', 'val_fld__3')
) as t(name_field,value_field);

关键说明： #

• json_object_agg(key, value)：将每行的name_field作为键，对应的JSON结构作为值，直接聚合为一个完整的JSON对象
• coalesce(t.value_field, '')：把null值转为空字符串，符合目标格式要求
• json_build_array(...)：将单个{"value":..., "seq":1}对象包装成数组，匹配目标结构

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