Importing from a JSON file
(1.1) By Simon Slavin (slavin) on 2022-11-06 12:39:11 edited from 1.0 [source]
Suppose I have a JSON file which is perfectly suited to import into a SQLite database. Can I script the SQLite CLI to import it, or is it simpler to write my own utility ? Ideally two answers:
- if there is no existing database with a table in it
- if there is an existing database with a table which has the right columns but no rows
(2) By Harald Hanche-Olsen (hanche) on 2022-11-06 08:11:12 in reply to 1.0 [link] [source]
I've had some success importing json into a SQLite database just using the command shell. But if the json is not very simple, it's a bit tedious and error prone, so you're possibly better off programming it in python or some such tool.
To import into a table with just one column, I do
.mode line
.import "|jq -c . filename.json" tablename
The purpose of running it through jq -c . is to ensure that each json object comes on a single line of input.
If you want to unpack the json to populate a table, create a single column view on the table, then create a INSTEAD OF INSERT ON trigger on the view to unpack the json object and insert the result into the real table (this is where “tedious and error prone” enters the equation. ;-)) and import into the view
(4) By Simon Slavin (slavin) on 2022-11-06 13:02:45 in reply to 2 [link] [source]
Good tip. Just get each line in as a JSON unit, and worry about splitting it into columns later. Didn't think of that. Thanks.
(3) By Warren Young (wyoung) on 2022-11-06 12:58:56 in reply to 1.1 [link] [source]
Pull each record into a single-column temp table, then use SQLite’s own JSON support to transmogrify the data into the persistent DB.