如何通过单条SQLite查询从两张表构建嵌套对象？

本人无SQLite使用背景，编写查询语句时遇到困难。现有两张简化表结构：

Table: EVENTS

Table: TIMESLOTS

希望生成如下格式的嵌套对象：

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

目前可通过两次查询再用代码逻辑构建该对象：

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

但希望仅用一次查询借助数据库能力，而非依赖JS处理。请问是否有简便方法？看起来JOIN无法实现这类对象的构建。

SQLite 3.38.0及以上版本支持JSON函数，可通过JSON_GROUP_ARRAY、JSON_OBJECT结合关联查询实现需求，无需额外代码处理。

基础查询（返回拆分字段） #

SELECT
  e.id,
  e.content,
  JSON_GROUP_ARRAY(
    JSON_OBJECT('start', t.start, 'end', t.end)
  ) AS timeslots
FROM events e
LEFT JOIN timeslots t ON e.id = t.id
GROUP BY e.id, e.content;

语句说明：

• LEFT JOIN：关联两张表，确保无对应时段的事件也能被返回（此时timeslots为[]）
• JSON_OBJECT：将单条时段数据转为JSON对象
• JSON_GROUP_ARRAY：将同一事件下的所有时段对象聚合为JSON数组
• GROUP BY：按事件的id和content分组，保证每个事件仅返回一条结果

查询结果示例：

直接返回完整嵌套JSON #

如果需要直接得到目标格式的完整JSON对象，可进一步包装：

SELECT
  JSON_OBJECT(
    'id', e.id,
    'content', e.content,
    'timeslots', JSON_GROUP_ARRAY(JSON_OBJECT('start', t.start, 'end', t.end))
  ) AS event_with_timeslots
FROM events e
LEFT JOIN timeslots t ON e.id = t.id
GROUP BY e.id, e.content;

执行后event_with_timeslots字段就是你需要的嵌套结构JSON。

内容的提问来源于stack exchange，提问作者charnould