如何在PostgreSQL查询中将JOIN结果合并为单个属性？

假设存在如下数据库结构与数据：

CREATE SCHEMA IF NOT EXISTS my_schema;

CREATE TABLE IF NOT EXISTS my_schema.my_table_a (
    id serial PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS my_schema.my_table_b (
    id serial PRIMARY KEY,
    my_table_a_id BIGINT REFERENCES my_schema.my_table_a (id) NOT NULL
);

INSERT INTO my_schema.my_table_a VALUES
    (1);

INSERT INTO my_schema.my_table_b VALUES
    (1, 1),
    (2, 1),
    (3, 1);

执行以下查询：

SELECT
    ta.*,
    tb as tb
FROM my_schema.my_table_a ta
LEFT JOIN my_schema.my_table_b tb
    ON ta.id = tb.my_table_a_id;

得到结果：

[
    {
        "id": 1,
        "tb": {
            "id": 1,
            "my_table_a_id": 1
        }
    },
    {
        "id": 1,
        "tb": {
            "id": 2,
            "my_table_a_id": 1
        }
    },
    {
        "id": 1,
        "tb": {
            "id": 3,
            "my_table_a_id": 1
        }
    }
]

需要修改查询，得到如下格式的结果：

[
    {
        "id": 1,
        "tb": [
            {
                "id": 1,
                "my_table_a_id": 1
            },
            {
                "id": 2,
                "my_table_a_id": 1
            },
            {
                "id": 3,
                "my_table_a_id": 1
            }
        ]
    }
]

使用PostgreSQL的json_agg()聚合函数，将每个my_table_a对应的多条my_table_b记录聚合为一个JSON数组，同时按my_table_a.id分组即可实现需求：

SELECT
    ta.*,
    json_agg(tb) AS tb
FROM my_schema.my_table_a ta
LEFT JOIN my_schema.my_table_b tb
    ON ta.id = tb.my_table_a_id
GROUP BY ta.id;

说明 #

• json_agg(tb)会自动将每条关联的tb行转换为JSON对象，并将这些对象聚合为一个JSON数组。
• 由于ta.id是主键，GROUP BY ta.id时，PostgreSQL会自动包含my_table_a的所有字段，无需手动列出。
• 如果存在my_table_a没有对应my_table_b记录的情况，json_agg会返回空数组[]，符合LEFT JOIN的预期行为。

内容的提问来源于stack exchange，提问作者Mike K