How to convert a JSON file to an SQLite database

A way do this without CSV or a 3rd party tool is to use the JSON1 extension of SQLite combined with the readfile extension that is provided in the sqlite3 CLI tool. As well as overall being a “more direct” solution, this has the advantage of handling JSON NULL values more consistently than CSV, which will otherwise import them as empty strings.

If the input file is a well-formed JSON file, e.g. the example given as an array:

[
{"uri":"/","user_agent":"example1"},
{"uri":"/foobar","user_agent":"example1"},
{"uri":"/","user_agent":"example2"},
{"uri":"/foobar","user_agent":"example3"}
]

Then this can be read into the corresponding my_table table as follows. Open the SQLite database file my_db.db using the sqlite3 CLI:

sqlite3 my_db.db

then create my_table using:

CREATE TABLE my_table(uri TEXT, user_agent TEXT);

Finally, the JSON data in my_data.json can be inserted into the table with the CLI command:

INSERT INTO my_table SELECT 
  json_extract(value, '$.uri'), 
  json_extract(value, '$.user_agent')
FROM json_each(readfile('my_data.json'));

If the initial JSON file is newline separated JSON elements, then this can be converted first using jq using:

jq -s <my_data_raw.json >my_data.json

It’s likely there is a way to do this directly in SQLite using JSON1, but I didn’t pursue that given that I was already using jq to massage the data prior to import to SQLite.