SQLite中提取嵌套JSON字段生成角色-配音关联表的方法

要处理titles表中嵌套JSON数组的提取需求，你需要通过**两层JSON_EACH**来逐层展开嵌套结构：

1. 第一层用JSON_EACH(titles.characters)展开characters数组，得到单个角色对象；
2. 第二层用JSON_EACH(JSON_EXTRACT(char.value, '$.voiceActor'))展开每个角色里的voiceActor数组，得到单个配音演员对象。

最终的SQL查询如下：

SELECT
    JSON_EXTRACT(char.value, '$.characterId') AS character_id,
    JSON_EXTRACT(va.value, '$.voiceActorId') AS voice_actor,
    JSON_EXTRACT(va.value, '$.language') AS language,
    t.mal_id AS title_id
FROM
    titles t,
    JSON_EACH(t.characters) char,
    JSON_EACH(JSON_EXTRACT(char.value, '$.voiceActor')) va

关键说明 #

• 给JSON_EACH的结果起别名（char、va），避免字段名冲突；
• 第二层JSON_EACH的数据源是第一层角色对象中的voiceActor数组，通过JSON_EXTRACT(char.value, '$.voiceActor')获取；
• 单个配音演员的字段（voiceActorId、language）需要从第二层展开的va.value中提取，而不是直接从角色对象中取。

执行这个查询后，就能得到每行对应一个角色ID、配音演员ID、语言、作品ID的关联表，完全符合你预期的格式。

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