CODEWITHSUNDEEP
postgresql
user1661677
user1661677

Reputation: 1272

How do I join tables while putting the results in a json array?

Table name: people

+----+------+-------------+-------+
| id | name | city        | state |
+----+------+-------------+-------+
| 1  | Joe  | Los Angeles | CA    |
+----+------+-------------+-------+
| 2  | Jill | Miami       | FL    |
+----+------+-------------+-------+
| 3  | Asa  | Portland    | OR    |
+----+------+-------------+-------+

Table name: pets

+----+----------+------+
| id | pet_name | type |
+----+----------+------+
| 1  | Spike    | dog  |
+----+----------+------+
| 1  | Fluffy   | cat  |
+----+----------+------+
| 2  | Oscar   | dog  |
+----+----------+------+

How would I join the two tables above to include a column containing JSON of results matched in the 'pets' table (PostgreSQL)?

+----+------+------------------------------------------------------------+
| id | name | pets                                                       |
+----+------+------------------------------------------------------------+
| 1  | Joe  | [{name:'Spike', type:'dog'}, {name: 'Fluffy', type:'cat'}] |
+----+------+------------------------------------------------------------+
| 2  | Jill | [{name:'Oscar', type:'dog'}]                               |
+----+------+------------------------------------------------------------+
| 3  | Asa  | []                                                         |
+----+------+------------------------------------------------------------+

Upvotes: 1

Views: 24

Answers (1)

Frank Heikens
Frank Heikens

Reputation: 127476

Use json_agg() to aggregate over json-objects:

   SELECT   people.id
        ,   name
        ,   json_agg(
                CASE WHEN pet_name IS NOT NULL THEN
                    json_build_object(
                        'name', pet_name
                    ,   'type', type
                )
                END
            )
    FROM    people
        LEFT JOIN pets ON people.id = pets.id
    GROUP BY
        people.id
        , name
    ORDER BY
        people.id;

Upvotes: 2

Related Questions