json - 如何从 postgresql 查询生成嵌套的 json 并用行数信息包装它？

Question

有了这个简单的关系模式:

CREATE TABLE district (
    id  SERIAL PRIMARY KEY,
    loc TEXT
);

CREATE TABLE person (
    id          SERIAL PRIMARY KEY,
    name        TEXT,
    district_id INTEGER NOT NULL REFERENCES district(id)
);

我需要一个用于 API 分页目的的查询，它会生成如下内容:

{
    "total_rows": 37,
    "list": [
        {
            "id": 4,
            "name": "Rebecca Jaskolski",
            "district": {
                "id": 3,
                "loc": "Albastad"
            }
        },
        {
            "id": 5,
            "name": "Newton Weissnat",
            "district": {
                "id": 4,
                "loc": "West Myronchester"
            }
        }
    ]
}

我现在生成具有上述形状的 JSON 输出的查询是这样的:

SELECT row_to_json(a) FROM (
    SELECT (
        SELECT COUNT(*) FROM person
    ) AS total_rows, (
        SELECT json_agg(row_to_json(t)) AS persons FROM (
            SELECT person.id, person.name, (
                SELECT row_to_json(d) AS district FROM (
                    SELECT district.id, district.loc FROM district where district.id = person.district_id
                ) d
            ) FROM
        ) t
    ) AS list
) a;

如您所见，上面的查询执行两个查询，COUNT 和实际查询。如果数据库变大，效率会很低吗？

那么，有没有更好的办法呢？

Answer 1

使用 json_build_object()。在我看来，这是构建嵌套 json 结构的最简单、最灵活的方法。

select json_build_object(
    'total_rows', count(*), 
    'list', json_agg(person)) as persons
from (
    select json_build_object(
        'id', p.id, 
        'name', name, 
        'district', json_build_object('id', d.id, 'loc', d.loc)) person
    from person p
    join district d on d.id = p.district_id
    ) s

Test it here. .