Search code examples
sqlsqlitejson-extract

Extracting nested JSON field property Sqlite3

Fiddle link: https://www.db-fiddle.com/f/u6ZXKW8TgFkDH5o2FhppgD/0

I have a query:

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

that returns

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

Expected results

I want to extract the voiceActorId, and language and add a new column to create a reference table to my voice actors to titles as well as their characterId they play.

I am new to this, and I am struggling to format a more complex query to access the nested values.

I thought I could do JSON_EXTRACT(value, '$.voiceActor.language') AS language as I come from a mongodb background. This doesn't seem to work in a way I would expect.

How can I extract these next fields to ultimately end up with a row like

character_id voice_actor language title_id
1 24 English 4
1 21 Japanese 4

So that each character, has a entry for a different language voice actor?

Setting up local SQL to reproduce:

Seed row

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"}]');
  • run this query
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);

Expected format:

character_id voice_actor language title_id
1 24 English 4
1 21 Japanese 4
Solution 
  • -- iterates over 'characters' column making table rows from json
with character_rows as (
  select
       mal_id AS title_id,
       value as character_json,
       json_extract(value, '$.characterId') as character_id
  from titles, 
       json_each(titles.characters)
)
-- iterates over 'voiceActor' arrays inside each json (in 'character_rows')
select
    title_id, 
    character_id,
    json_extract(value, '$.language') as language,
    json_extract(value, '$.voiceActorId') as voice_actor_id
from character_rows cr,
     json_each(cr.character_json, '$.voiceActor');

    The main idea is that we divide characters json into smaller chunks using json_each() function and get smaller json objects per "characterId" field.
    Then we go deeper inside each smaller json and iterate over inner array "characterId" and call necessary fields.

    Details:

    • CTE runs the query to create rows out of characters json column per each "characterId".
    • The second query goes inside each character_json field and iterates over inner array with the path '$.voiceActor'.
      Then it gets '$.language' and '$.voiceActorId' fields from each $.voiceActor array element.

    See the dbfiddle link.