CODEWITHSUNDEEP
sqlpostgresqlmassive
Vincent Palmer
Vincent Palmer

Reputation: 23

Joining a PostgreSQL table into another table's column

I'm looking for a way to join(?) two tables together, but not in the traditional way of matching foreign keys and creating a row of the match.

For example, if I have a person table and a cars table:

Table person
| name | age | cars |
| ---- | --- | ---- |
| Mike | 41  |  {}  |

Table cars
| owner | make | model | year |
| ----- | ---- | ----- | ---- |
| Mike  | Honda| Civic | 2012 |
| Mike  | Ford | Focus | 2018 |

Is it possible to query a result that looks like this:

{
    name: 'Mike',
    age: 41,
    cars: [{
        make: 'Honda',
        model: 'Civic',
        year: 2012
    },
    {
        make: 'Ford',
        model: 'Focus',
        year: 2018
    }]
}

I'm using node/express/massive if that makes a difference. I'm pretty new to SQL, and for all I know this is a wild goose chase and isn't even possible, but if it is, I certainly don't know how.

Upvotes: 2

Views: 40

Answers (2)

Timshel
Timshel

Reputation: 1783

You can do this by nesting your calls to json_build_object():

select json_build_object(
        'name', p.name,
        'age', p.age,
        'cars': array_agg(json_build_object('car', car, 'model', model, 'year', year)) 
    ) as info
from person p left join
     cars c
     on p.name = c.owner
group by p.name, p.age;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270793

If I'm reading that correctly, you have an array of json objects. I find that curious, but you can build this in Postgres:

select p.name, p.age,
       array_agg(json_build_object('car', car, 'model', model, 'year', year)) as info
from person p join
     cars c
     on p.name = c.owner
group by p.name, p.age;

Upvotes: 1

Related Questions