SQLite中连接多关联表并将结果分组为JSON数组的查询方法

步骤1：统一所有关联表的数据格式 #

首先将不同类型的衣物关联表数据合并为统一结构的数据集，每个条目包含person_id、衣物类型type和衣物IDclothing_id。假设你的关联表分别为person_shirt、person_pant、person_shoe，可以用UNION ALL完成合并：

SELECT person AS person_id, 'shirt' AS type, shirt AS clothing_id FROM person_shirt
UNION ALL
SELECT person AS person_id, 'pant' AS type, pant AS clothing_id FROM person_pant
UNION ALL
SELECT person AS person_id, 'shoe' AS type, shoe AS clothing_id FROM person_shoe

合并后的数据结构示例：

+-----------+-------+-------------+
| person_id | type  | clothing_id |
+-----------+-------+-------------+
|         1 | shirt |           3 |
|         1 | shirt |           4 |
|         2 | shirt |           2 |
|         1 | pant  |           2 |
|         1 | shoe  |           5 |
+-----------+-------+-------------+

步骤2：自定义JSON数组聚合函数 #

SQLite 默认没有生成指定格式JSON数组的聚合函数，需要自定义一个。以下以Python的sqlite3模块为例实现：

import sqlite3
import json

def json_array_agg(accumulator, value):
    if accumulator is None:
        accumulator = []
    accumulator.append(json.loads(value))
    return accumulator

def finalize_json_array(accumulator):
    if not accumulator:
        return '[]'
    return json.dumps(accumulator)

# 注册自定义聚合函数到数据库连接
conn = sqlite3.connect('your_database.db')
conn.create_aggregate('json_array_agg', 2, json_array_agg, finalize=finalize_json_array)

该函数会将单个衣物的JSON对象收集到数组中，最终序列化为标准的JSON数组字符串。

步骤3：整合查询得到最终结果 #

将合并后的衣物数据与人员表关联，通过自定义聚合函数聚合每个人的所有衣物条目：

SELECT
    p.id,
    p.name,
    json_array_agg(json_object('type', c.type, 'id', c.clothing_id)) AS "clothing items"
FROM person p
LEFT JOIN (
    SELECT person AS person_id, 'shirt' AS type, shirt AS clothing_id FROM person_shirt
    UNION ALL
    SELECT person AS person_id, 'pant' AS type, pant AS clothing_id FROM person_pant
    UNION ALL
    SELECT person AS person_id, 'shoe' AS type, shoe AS clothing_id FROM person_shoe
) c ON p.id = c.person_id
GROUP BY p.id, p.name

关键说明： #

• 使用LEFT JOIN确保无衣物的人员（比如Jane）也会出现在结果中，对应clothing items为[]
• json_object('type', c.type, 'id', c.clothing_id)将单条衣物数据转换为{"type":"shirt","id":3}格式的JSON对象
• 自定义json_array_agg函数将单个JSON对象聚合为完整的JSON数组字符串

替代方案：无自定义函数的字符串拼接 #

如果不想自定义函数，也可以手动拼接字符串实现，但需要注意转义问题（比如类型名称含引号时会出错）：

SELECT
    p.id,
    p.name,
    '[' || GROUP_CONCAT('{"type":"' || c.type || '","id":' || c.clothing_id || '}', ',') || ']' AS "clothing items"
FROM person p
LEFT JOIN (
    SELECT person AS person_id, 'shirt' AS type, shirt AS clothing_id FROM person_shirt
    UNION ALL
    SELECT person AS person_id, 'pant' AS type, pant AS clothing_id FROM person_pant
    UNION ALL
    SELECT person AS person_id, 'shoe' AS type, shoe AS clothing_id FROM person_shoe
) c ON p.id = c.person_id
GROUP BY p.id, p.name

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