Fiddle link：https://www.db-fiddle.com/f/u6ZXKW8TgFkDH5o2FhppgD/0
我有一个疑问：

SELECT 
    JSON_EXTRACT(value, '$.characterId') AS character_id,
    JSON_EXTRACT(value, '$.voiceActor') AS voice_actor,
    JSON_EXTRACT(value, '$.voiceActor') AS language,
    mal_id AS title_id
FROM 
    titles,
    JSON_EACH(titles.characters)
LIMIT 1

它会返回

9054    
[{
    "name": "Grant, Tiffany",
    "image": "https://cdn.myanimelist.net/r/42x62/images/voiceactors/2/19267.jpg?s=00b00d8b80aad8939ee710a62d313d33",
    "language": "English",
    "voiceActorId": "145"
}, {
    "name": "Kiuchi, Reiko",
    "image": "https://cdn.myanimelist.net/r/42x62/images/voiceactors/3/48105.jpg?s=285c922efca770003fd7a374cfccec5e",
    "language": "Japanese",
    "voiceActorId": "447"
}, {
    "name": "Clinkenbeard, Colleen",
    "image": "https://cdn.myanimelist.net/r/42x62/images/voiceactors/1/30191.jpg?s=ae2728aa271ed956b5a0b72e7a047cbc",
    "language": "English",
    "voiceActorId": "472"
}]
[{
    "name": "Grant, Tiffany",
    "image": "https://cdn.myanimelist.net/r/42x62/images/voiceactors/2/19267.jpg?s=00b00d8b80aad8939ee710a62d313d33",
    "language": "English",
    "voiceActorId": "145"
}, {
    "name": "Kiuchi, Reiko",
    "image": "https://cdn.myanimelist.net/r/42x62/images/voiceactors/3/48105.jpg?s=285c922efca770003fd7a374cfccec5e",
    "language": "Japanese",
    "voiceActorId": "447"
}, {
    "name": "Clinkenbeard, Colleen",
    "image": "https://cdn.myanimelist.net/r/42x62/images/voiceactors/1/30191.jpg?s=ae2728aa271ed956b5a0b72e7a047cbc",
    "language": "English",
    "voiceActorId": "472"
}]
8

预期结果

我想提取voiceActorId和language，并添加一个新列来创建一个引用表，以将我的配音演员的标题以及他们扮演的characterId。
我是这方面的新手，我正在努力格式化一个更复杂的查询来访问嵌套的值。
我想我可以做JSON_EXTRACT(value, '$.voiceActor.language') AS language，因为我来自mongodb背景。这似乎不是我所期望的工作方式。
我如何提取这些下一个字段，最终得到一行
| 字符标识|配音演员|语言|标题_id|
| --------------|--------------|--------------|--------------|
| 1|二十四|英语|四|
| 1|二十一|简体中文|四|
所以每个角色，都有一个不同语言的配音演员？
正在设置本地SQL以复制：
种子行

INSERT INTO "mydb"."titles" ("_id", "mal_id", "url", "images", "trailer", "approved", "titles", "title", "title_english", "title_japanese", "title_synonyms", "type", "source", "episodes", "status", "airing", "aired", "duration", "rating", "score", "scored_by", "rank", "popularity", "members", "favorites", "synopsis", "background", "season", "year", "broadcast", "producers", "licensors", "studios", "genres", "explicit_genres", "themes", "demographics", "characters") VALUES ('{"$oid":"6394ba48d5fb73173e3c596f"}', '8', 'https://myanimelist.net/anime/8/Bouken_Ou_Beet', '{"jpg":{"image_url":"https://cdn.myanimelist.net/images/anime/7/21569.jpg","small_image_url":"https://cdn.myanimelist.net/images/anime/7/21569t.jpg","large_image_url":"https://cdn.myanimelist.net/images/anime/7/21569l.jpg"},"webp":{"image_url":"https://cdn.myanimelist.net/images/anime/7/21569.webp","small_image_url":"https://cdn.myanimelist.net/images/anime/7/21569t.webp","large_image_url":"https://cdn.myanimelist.net/images/anime/7/21569l.webp"}}', '{"youtube_id":null,"url":null,"embed_url":null,"images":{"image_url":null,"small_image_url":null,"medium_image_url":null,"large_image_url":null,"maximum_image_url":null}}', 'true', '[{"type":"Default","title":"Bouken Ou Beet"},{"type":"Synonym","title":"Adventure King Beet"},{"type":"Japanese","title":"冒険王ビィト"},{"type":"English","title":"Beet the Vandel Buster"}]', 'Bouken Ou Beet', 'Beet the Vandel Buster', '冒険王ビィト', '["Adventure King Beet"]', 'TV', 'Manga', '52', 'Finished Airing', 'false', '{"from":"2004-09-30T00:00:00+00:00","to":"2005-09-29T00:00:00+00:00","prop":{"from":{"day":30,"month":9,"year":2004},"to":{"day":29,"month":9,"year":2005}},"string":"Sep 30, 2004 to Sep 29, 2005"}', '23 min per ep', 'PG - Children', '6.95', '6314', '4195', '4970', '14642', '14', 'It is the dark century and the people are suffering under the rule of the devil, Vandel, who is able to manipulate monsters. The Vandel Busters are a group of people who hunt these devils, and among them, the Zenon Squad is known to be the strongest busters on the continent. A young boy, Beet, dreams of joining the Zenon Squad. However, one day, as a result of Beet''s fault, the Zenon squad was defeated by the devil, Beltose. The five dying busters sacrificed their life power into their five weapons, Saiga. After giving their weapons to Beet, they passed away. Years have passed since then and the young Vandel Buster, Beet, begins his adventure to carry out the Zenon Squad''s will to put an end to the dark century.', 'null', 'fall', '2004', '{"day":"Thursdays","time":"18:30","timezone":"Asia/Tokyo","string":"Thursdays at 18:30 (JST)"}', '[{"mal_id":16,"type":"anime","name":"TV Tokyo","url":"https://myanimelist.net/anime/producer/16/TV_Tokyo"},{"mal_id":53,"type":"anime","name":"Dentsu","url":"https://myanimelist.net/anime/producer/53/Dentsu"}]', '[{"mal_id":2262,"type":"anime","name":"Illumitoon Entertainment","url":"https://myanimelist.net/anime/producer/2262/Illumitoon_Entertainment"}]', '[{"mal_id":18,"type":"anime","name":"Toei Animation","url":"https://myanimelist.net/anime/producer/18/Toei_Animation"}]', '[{"mal_id":2,"type":"anime","name":"Adventure","url":"https://myanimelist.net/anime/genre/2/Adventure"},{"mal_id":10,"type":"anime","name":"Fantasy","url":"https://myanimelist.net/anime/genre/10/Fantasy"},{"mal_id":37,"type":"anime","name":"Supernatural","url":"https://myanimelist.net/anime/genre/37/Supernatural"}]', '[]', '[]', '[{"mal_id":27,"type":"anime","name":"Shounen","url":"https://myanimelist.net/anime/genre/27/Shounen"}]', '[{"characterId":"9054","characterName":"Beet","images":"https://cdn.myanimelist.net/r/42x62/images/characters/4/123155.jpg?s=71a949a12df96189b1203bfcbbda625a","voiceActor":[{"name":"Grant, Tiffany","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/2/19267.jpg?s=00b00d8b80aad8939ee710a62d313d33","language":"English","voiceActorId":"145"},{"name":"Kiuchi, Reiko","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/3/48105.jpg?s=285c922efca770003fd7a374cfccec5e","language":"Japanese","voiceActorId":"447"},{"name":"Clinkenbeard, Colleen","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/1/30191.jpg?s=ae2728aa271ed956b5a0b72e7a047cbc","language":"English","voiceActorId":"472"}],"role":"Main"},{"characterId":"9058","characterName":"Kissu","images":"https://cdn.myanimelist.net/r/42x62/images/characters/14/123149.jpg?s=d1b6a0ab7dece78a9ffc3ab001fc2611","voiceActor":[{"name":"Hisakawa, Aya","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/1/55009.jpg?s=0a90d5fa92cd90c29ff395e341e21a0a","language":"Japanese","voiceActorId":"80"},{"name":"Connolly, Kevin M.","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/1/22315.jpg?s=11d9a22dc2472156ac85598127a7a499","language":"English","voiceActorId":"858"}],"role":"Main"},{"characterId":"31656","characterName":"Milfa","images":"https://cdn.myanimelist.net/r/42x62/images/characters/15/123145.jpg?s=09bf0bb0d2b6900835563abbb14261a1","voiceActor":[{"name":"Shishido, Rumi","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/3/30343.jpg?s=511d84fdc66c840c467cfb77e30cb3f5","language":"Japanese","voiceActorId":"709"},{"name":"Clark, Leah","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/1/6770.jpg?s=46aa3da2ebe16b7221713f7a0315f562","language":"English","voiceActorId":"859"}],"role":"Main"},{"characterId":"9056","characterName":"Poala","images":"https://cdn.myanimelist.net/r/42x62/images/characters/4/123153.jpg?s=177f293cc43643d5ef976c163fe1557b","voiceActor":[{"name":"Christian, Luci","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/2/49236.jpg?s=731f7c9032263f267b4896750d2d8301","language":"English","voiceActorId":"189"},{"name":"Maeda, Ai","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/2/33445.jpg?s=3f4772cef4439908f3a1e943ececa408","language":"Japanese","voiceActorId":"487"}],"role":"Main"},{"characterId":"31657","characterName":"Slade","images":"https://cdn.myanimelist.net/r/42x62/images/characters/10/123147.jpg?s=30b2227939a0cb38428a4bfa78021979","voiceActor":[{"name":"Miura, Hiroaki","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/1/16299.jpg?s=b29def98aef2be81fe7574127c843189","language":"Japanese","voiceActorId":"1526"}],"role":"Main"},{"characterId":"14469","characterName":"Beltoze","images":"https://cdn.myanimelist.net/r/42x62/images/characters/14/123143.jpg?s=b0a77f29d982a66631254ffae7f5424c","voiceActor":[{"name":"Ishizuka, Unshou","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/2/17135.jpg?s=5925123b8a7cf9b51a445c225442f0ef","language":"Japanese","voiceActorId":"357"},{"name":"Jenkins, Bill","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/1/14253.jpg?s=c3fc096db00e1b42d76129c291e17a2d","language":"English","voiceActorId":"9867"}],"role":"Supporting"},{"characterId":"171787","characterName":"Cruz","images":"https://cdn.myanimelist.net/r/42x62/images/questionmark_23.gif?s=f7dcbc4a4603d18356d3dfef8abd655c","voiceActor":[{"name":"Chiba, Susumu","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/2/55045.jpg?s=b573e4450ea0f18317111fc14be0de01","language":"Japanese","voiceActorId":"260"}],"role":"Supporting"},{"characterId":"8931","characterName":"Grunide","images":"https://cdn.myanimelist.net/r/42x62/images/characters/6/123141.jpg?s=80e4708931c7dd76b40f7528312171ff","voiceActor":[{"name":"Ootomo, Ryuuzaburou","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/2/10127.jpg?s=50713fb59858af5a9668701332ee53b3","language":"Japanese","voiceActorId":"836"},{"name":"Cason, Chris","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/2/19749.jpg?s=0e65042064237840447a4855571a871f","language":"English","voiceActorId":"1138"}],"role":"Supporting"},{"characterId":"90495","characterName":"Shagi","images":"https://cdn.myanimelist.net/r/42x62/images/characters/14/217589.jpg?s=744c6c35f52fb34f69787a958c186a64","voiceActor":[{"name":"Nakao, Ryusei","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/1/65678.jpg?s=492c982f157200ceb1c87c3e7d5c72d3","language":"Japanese","voiceActorId":"259"}],"role":"Supporting"},{"characterId":"16570","characterName":"Zenon","images":"https://cdn.myanimelist.net/r/42x62/images/characters/9/126283.jpg?s=aca9f14898680c557a66f548718ee147","voiceActor":[{"name":"Midorikawa, Hikaru","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/2/56626.jpg?s=1126959003f1ca2f352d96d74df8cfea","language":"Japanese","voiceActorId":"112"},{"name":"Swasey, John","image":"https://cdn.myanimelist.net/r/42x62/images/voiceactors/2/12446.jpg?s=3716111b135b8c88c020e21dd2e2e53b","language":"English","voiceActorId":"201"}],"role":"Supporting"}]');

• 运行此查询

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

预期格式：
| 字符标识|配音演员|语言|标题_id|
| --------------|--------------|--------------|--------------|
| 1|二十四|英语|四|
| 1|二十一|简体中文|四|