Extracting nested JSON field property Sqlite3

Question

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

Hi all, thanks! I didn't realize it wasn't enough to go on. Thanks for the kind comments. I updated now with a row, hopefully this should be enough seed data. Let me know if there is anymore context I can provide. — appleman, May 13 '23 at 15:30
Hi! It would be nice if you provide dbfiddle script with `create` table and `insert` statements so people can just run the script with sample data and think about solution to your question. — Albina, May 13 '23 at 16:28
after cleaning your data up you need also to extraxt json values from the array voiceActor — nbk, May 13 '23 at 17:35
Ah I didnt know about dbfiddle. I can try setting that up @Albina. nbk I am not sure show to go about doing that. would you be able to explain with an example? — appleman, May 13 '23 at 17:43
@Albina here is the fiddle https://www.db-fiddle.com/f/u6ZXKW8TgFkDH5o2FhppgD/0 — appleman, May 13 '23 at 18:31

Albina · Accepted Answer · 2023-05-13T21:40:32.170

2

-- 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.

edited May 13 '23 at 21:40

answered May 13 '23 at 21:28

Albina

1,901
3
7
19

This is exactly what I was looking for! I am still trying to understand how these all interact with each other, but this is very helpful in helping me understand the json_extract functionality. Do you have any suggestions on how sites that I could learn more from? Thanks! awesome work, – appleman May 13 '23 at 22:09
@appleman Thank you! Glad I could help! I just divided a big problem into smaller problems. **First**, I understood the json structure (with the help of _beautify json_ tools) to get all the correct json paths according to your requested columns. **Then**, I looked into the official documentation about json functions in sqlite and studied the examples/tutorials ([documentation](https://www.sqlite.org/json1.html), [examples](https://database.guide/sqlite-json_extract/)). **Lastly**, I started experimenting with json functions and your actual json :) – Albina May 14 '23 at 08:33
Wow, the examples website is great! totally missed this one. Thanks again, you really help me understand this core concept. – appleman May 14 '23 at 13:50

Extracting nested JSON field property Sqlite3

1 Answers1