我有以下形式的数据
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}]}
我试着这样做:
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);
但这并没有给我带来想要的结果. 如何实现所需的json格式?