SQLite Is Not a Toy Database
Fleeting- External reference: https://antonz.org/sqlite-is-not-a-toy-database/
SQLite is not a toy database
You can select data directly from a file as if it were a regular table. Or import data into the table and select from there.
select json_extract(value, ‘$.iso.code’) as code, json_extract(value, ‘$.iso.number’) as num, json_extract(value, ‘$.name’) as name, json_extract(value, ‘$.units.major.name’) as unit from json_each(readfile(‘currency.sample.json’)) ;
┌──────┬─────┬─────────────────┬──────────┐ │ code │ num │ name │ unit │ ├──────┼─────┼─────────────────┼──────────┤ │ ARS │ 032 │ Argentine peso | peso │ │ CHF │ 756 │ Swiss Franc │ franc │ │ EUR │ 978 │ Euro │ euro │ │ GBP │ 826 │ British Pound │ pound │ │ INR │ 356 │ Indian Rupee │ rupee │ │ JPY │ 392 │ Japanese yen │ yen │ │ MAD │ 504 │ Moroccan Dirham │ dirham │ │ RUR │ 643 │ Russian Rouble │ rouble │ │ SOS │ 706 │ Somali Shilling │ shilling │ │ USD │ 840 │ US Dollar │ dollar │ └──────┴─────┴─────────────────┴──────────┘
Doesn’t matter how deep the JSON is - you can extract any nested object