How to build a nested object from 2 SQLite tables?

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.