CODEWITHSUNDEEP
sqlpostgresqlrelationship
user16385449
user16385449

Reputation:

How to join 2 tables and have a list of other table's values nested in the first one in PostgreSQL?

Let's assume that we have these 2 tables: person and car

CREATE TABLE person (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE car (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    make VARCHAR NOT NULL,
    person_id BIGINT NOT NULL REFERENCES person(id)
);

What I try to do is to find all people, find every car and create an array of objects like this one

[
    {
        "id": "PERSON_ID",
        "name": "PERSON_NAME",
        "cars": [
            {
                "id": "CAR_ID",
                "model": "MODEL_NAME",
                "person_id": "PERSON_ID"
            }
        ]
    }
]

I have tried using the AS alias with a JOIN on person table from car table but it didn't work. Is there a way to do this? Thank you!

Upvotes: 1

Views: 1025

Answers (2)

ggordon
ggordon

Reputation: 10035

You may try the following. See a working fiddle:

Schema (PostgreSQL v13)

CREATE TABLE person (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE car (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    make VARCHAR NOT NULL,
    person_id BIGINT NOT NULL REFERENCES person(id)
);

insert into person(name) values ('tom'),('harry');

insert into car (id,make,person_id) values (1,'ford',1),(2,'audi',1),(3,'nissan',2);

Query #1

SELECT
   p.id,
   p.name,
   array_agg(row_to_json(c)) cars
FROM
   person p
INNER JOIN
   (SELECT id, make model, person_id FROM car) c ON p.id = c.person_id
GROUP BY
   p.id,
   p.name;
id name cars
1 tom [{"id":1,"model":"ford","person_id":1},{"id":2,"model":"audi","person_id":1}]
2 harry [{"id":3,"model":"nissan","person_id":2}]

View on DB Fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

If you want the result set as an array in Postgres, you can use:

select p.*, array_agg(c)
from person p join
     car c
     on c.person_id = p.id
group by p.id;

You can do something similar if you want JSON returned as well.

Upvotes: 1

Related Questions