时间戳

首页 > 解决方案 > 如何从具有一对多关系的表中构建 JSON 行

sql - 如何从具有一对多关系的表中构建 JSON 行

问题描述

我有一张main这样的桌子：

create foreign table main (
    "id" character varying not null,
    "a" character varying not null,
    "b" character varying not null
)

我还有另一张桌子not_main，像这样：

create foreign table not_main (
    "id" character varying not null,
    "fk" character varying not null,
    "d" character varying not null,
    "e" character varying not null
)

我是否想要一个返回如下的查询：

   json
0  {"id": "id_main_0", "a": "a0", "b": "b0", "cs": [{"id": "id_not_main_0", "fk": "id_main_0", "d": "d0", "e": "e0"}, {"id": "id_not_main_1", "fk": "id_main_0", "d": "d1", "e": "e1"}]}
1  {"id": "id_main_1", "a": "a1", "b": "b1", "cs": [{"id": "id_not_main_2", "fk": "id_main_1", "d": "d2", "e": "e3"}, {"id": "id_not_main_3", "fk": "id_main_1", "d": "d3", "e": "e3"}]}

我该怎么做？

我试过了：

select
    json_build_object(
        'id', m."id",
        'a', m."a",
        'b', m."b",
        'cs', json_build_array(
            json_build_object(
                'd', nm."d",
                'e', nm."e"
            )
        ) 
    )
from main m
left join not_main nm on
    nm."requisitionId" = m.id;

但它只返回一个元素cs：

   json
0  {"id": "id_main_0", "a": "a0", "b": "b0", "cs": [{"id": "id_not_main_0", "fk": "id_main_0", "d": "d0", "e": "e0"}]}
1  {"id": "id_main_1", "a": "a1", "b": "b1", "cs": [{"id": "id_not_main_2", "fk": "id_main_1", "d": "d2", "e": "e3"}]}

OBS：考虑和之间的约束main和not_main是正确建模的，例如，我实际上将两列都id作为 PK 并且fk引用.idmain

标签： sqlarraysjsonpostgresqlgroup-by

解决方案

你想要 json 数组聚合。基本上，您只需要更改json_build_array()为json_agg(), 并添加一个group by子句：

select
    json_build_object(
        'id', m.id,
        'a', m.a,
        'b', m.b,
        'cs', json_agg(
            json_build_object(
                'd', nm.d,
                'e', nm.e
            )
        ) 
    )
from main m
left join not_main nm on
    nm.requisitionId = m.id
group by m.id, m.a, m.b

推荐阅读