如何从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

在这里测试..