How to return result of a join into a single property in a Postgres query?-CodePudding

Suppose the following,

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);

If I run the following query,

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;

Then the result is,

[
    {
        "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
        }
    }
]

How can I get it to work like this:

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

CodePudding user response：

SELECT
ta.*,
ARRAY_AGG(tb) AS tb
FROM my_schema.my_table_a ta, my_schema.my_table_b tb
GROUP BY ta.id
ORDER BY ta.id;

Example https://www.db-fiddle.com/f/5i97YZ6FMRY48pZaJ255EJ/0