How to build a nested object from 2 SQLite tables?

April 28, 2023

Having not a SQLite background, I’m struggling building a "simple" SQLite query.
Considering the simplified schema below:

** Table: EVENTS **
id   content
1    abc
2    xyz

** Table: TIMESLOTS **
id   start     end
1    05-2022   06-2022
1    10-2022   12-2022
2   .......  .......

I’d like to build object like this on:

{
  id: 1,
  content: abc,
  timeslots:
    [
     {start: 05-2022, end: 06-2022},
     {start: 10-2022, end: 12-2022},
     ...
    ]
},
...

I’am able to do it in 2 queries and then build the object programaticaly (easy!)

SELECT * FROM events WHERE id = 1
SELECT * FROM timeslots WHERE id = 1

But I’d like to make it in 1 to leverage DB power, not JS.
Is there a simple way to do it? JOIN doesn’t seem to allow me building this kind of object.

>Solution :

You can join the tables, aggregate by id and use SQLite’s JSON functions to build the json object:

SELECT json_object(
         'id', e.id,
         'content', e.content,
         'timeslots', json_group_array(
                        json_object(
                          'start', t.start,
                          'end', t.end
                        )
                      )
       ) col
FROM events e INNER JOIN timeslots t
ON t.id = e.id
-- WHERE e.id = ?
GROUP BY e.id;

See the demo.