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