sql - 获取sql查询结果为json

Question

我有一个包含两列 A 和 B 的表测试，并从中创建表 1 和表 2。

 test          table1              table2
A   B         A  count(A)       B  count(B)   A
95  1         95   7            1     3       95
 5  11         5   2            11    2        5
95  1                           9     4       95
95  9
95  1
95  9
 5  11
95  9
95  9

如何得到这样的结果:

{"node": [   
    {"child": [
              {"value": 3,
               "name": "1"},
              {"value": 4,
               "name": "9"}],
       "value": 7,
       "name": "95"},
   {"child": [
             {"value": 2,
              "name": "11"}],
      "value": 2,
      "name": "5"}],
 "name": "test",
 "value": 9}

首先，我按 A 列分组并计算名称 =“95”、值 =7 和名称 =“5”、值 =2 的组。对于每个组，我也计算 B 列。有很多 json函数，但直到现在我都不知道如何得到上面的结果。

查询应该类似于:

select row_to_json(t) from ( select * , ( select array_to_json(array_agg(row_to_json(u))) from ( select * from table1 where table1.a=table2.a ) as u ) from table2 ) as t;

Answer 1

您可以使用 plpgsql 函数生成正确的 json。这不是很困难，虽然有时有点乏味。检查这个(将 tt 重命名为实际表名):

create or replace function test_to_json()
returns json language plpgsql
as $$
declare
    rec1 record;
    rec2 record;
    res text;
begin
    res = '{"node": [';
    for rec1 in
        select a, count(b) ct
        from tt
        group by 1
    loop
        res = format('%s{"child": [', res);
        for rec2 in
            select a, b, count(b) ct
            from tt
            where a = rec1.a
            group by 1,2
        loop
            res = res || format('{"value": %s, "name": %s},', rec2.ct, rec2.b);
        end loop;
        res = rtrim(res, ',');
        res = format('%s],"value": %s, "name": %s},', res, rec1.ct, rec1.a);
    end loop;
    res = rtrim(res, ',');
    res = format('%s], "value": %s}', res, (select count(b) from tt));
    return res:: json;
end $$;

select test_to_json();