sqlpostgresqlmassive

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.


Solution

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