如何在SQLite中查询分组关联表并返回JSON格式结果

表结构说明 #

假设有三张表：

人员表 people #

+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Mary |
|  3 | Jane |
+----+------+

衣物表（以鞋子表shoes为例，其他衣物表结构类似） #

+----+----------+--------------------+---------+
| id |  brand   |        name        |  type   |
+----+----------+--------------------+---------+
|  1 | Converse | High tops          | sneaker |
|  2 | Clarks   | Tilden cap Oxfords | dress   |
|  3 | Nike     | Air Zoom           | running |
+----+----------+--------------------+---------+

关联表（存储人员与衣物的拥有关系） #

+--------+--------+-------+-------+
| person | shirts | pants | shoes |
+--------+--------+-------+-------+
|      1 |      3 |       |       |
|      1 |      4 |       |       |
|      1 |        |     3 |       |
|      1 |        |       |     5 |
|      2 |        |     2 |       |
|      2 |        |       |     2 |
|      2 |      3 |       |       |
...

需求 #

编写SQLite查询语句，将关联表数据汇总为如下格式：

+----+------+--------------------+
| id | name |   clothing items   |
+----+------+--------------------+
|  1 | John | [JSON字符串格式]    |
|  2 | Mary | [JSON字符串格式]    |
|  3 | Jane | [JSON字符串格式]    |
+----+------+--------------------+

其中clothing items列的JSON格式示例：

{
  "shirts":[3,4],
  "pants":[3],
  "shoes":[5]
}

实现语句 #

注意：SQLite 3.33.0及以上版本支持JSON_OBJECT、JSON_GROUP_ARRAY等JSON聚合函数，若版本低于此需先升级。

查询语句如下：

SELECT
  p.id,
  p.name,
  JSON_OBJECT(
    'shirts', COALESCE((SELECT JSON_GROUP_ARRAY(shirts) FROM 关联表 WHERE person = p.id AND shirts IS NOT NULL), '[]'),
    'pants', COALESCE((SELECT JSON_GROUP_ARRAY(pants) FROM 关联表 WHERE person = p.id AND pants IS NOT NULL), '[]'),
    'shoes', COALESCE((SELECT JSON_GROUP_ARRAY(shoes) FROM 关联表 WHERE person = p.id AND shoes IS NOT NULL), '[]')
  ) AS "clothing items"
FROM people p
LEFT JOIN 关联表 r ON p.id = r.person
GROUP BY p.id, p.name;

语句说明 #

• JSON_OBJECT：构建最终的JSON对象，键为衣物类型，值为对应的ID数组。
• JSON_GROUP_ARRAY：将同一人员的同类型衣物ID聚合为JSON数组。
• COALESCE：处理无对应衣物的情况，返回空数组[]而非NULL。
• LEFT JOIN + GROUP BY：确保即使人员没有任何衣物记录（如Jane），也会出现在结果中。

如果关联表有重复的衣物ID（如Mary的pants记录有两条2），若需要去重可以使用JSON_GROUP_ARRAY(DISTINCT pants)替代JSON_GROUP_ARRAY(pants)。

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