Say I have a table of entries and a 2nd table of related entries:

Table 1:

Say I have a table of entries and a 2nd table of related entries:

Table 1:

Table 2:

Is there a way to write a query that returns data like this:

[
  { 
    id: 1,
    data: foo,
    related_stuff: [
      { 
        id: 1,
        data: ding
      },
      { 
        id: 2,
        data: dong
      }
    ]
  },
  {
    id: 2,
    data: bar,
    related_stuff: []
  }
]

There are aggregate functions that combine single entries together to one aggregated list, mostly returned as strings, but I just want the entire dataset as an object (I work with JS mostly but the kind of language the database is bound to should be irrelevant here).

Example:

SELECT t1.id as id, t1.data as data, COUNT(t2.id) as amount
FROM t1
  LEFT JOIN t2 ON t1.id = t2.related_to
GROUP BY t1.id

I would think something akin to this:

SELECT t1.id as id,
       t1.data as data,
       SELECT * FROM t2
           WHERE t2.related_to = t1.id
       as related_stuff
FROM t1

But I get an error that entries from the subquery must have at most 1 column.

Right now our code simply queries the entries of Table 1 and then does a query to Table 2 for each entry returned by Table 1 but I feel like there should be a way to write a single SQL query that returns the entirety in one query.

• sql

• 3 Which dbms are you using? – jarlh Commented Feb 10 at 12:29
• I felt like this is such a basic thing to do I would have guessed that does not matter. I work with Postgres and sqlite at the moment but I feel like this is so basic it should have a common syntax for all backends. – salbeira Commented Feb 10 at 12:30
• 2 The simple answer is that SQL is not designed that way. A query can return a table consisting of columns of simple data types (int, string, date, ...), but not columns containing nested tables. Depending on the DBMS you are using, there may be ways to work around that (JSON typically, sometimes cursors or objects). – Thorsten Kettner Commented Feb 10 at 13:01
• Usually the idea is to either do one big query. Or to use two separate queries, where the second returns the parent PKs of the first query (no need to do N second queries, just a single query with the same WHERE). In both cases, use a hashmap/dictionary to put related_stuff columns into a nested child list. SQL just doesn't work the way you are thinking, that's more like an object database such as MongoDB. – Charlieface Commented Feb 10 at 13:04
• @Charlieface So do I understand correctly I would do two queries and just fiddle together the data afterwards myself into the correct t1 object? Is that quicker than doing individual SQL queries? I would still need to do n x m compares with the results. – salbeira Commented Feb 10 at 13:15

1 Answer 1

Actually, it would vary based on the dbms.

For postgres you can try using json_agg in this way.

SELECT 
    t1.id, 
    t1.data, 
    COALESCE(json_agg(
        json_build_object('id', t2.id, 'data', t2.data)
    ) FILTER (WHERE t2.id IS NOT NULL), '[]') AS related_stuff
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.related_to
GROUP BY t1.id, t1.data;

But, this wouldn't work with sqlite, that has something called json_group_array.