Convert json to a sqlite database for querying

json-to-sqlite.md

Sometimes, you need to quickly dump a json file into sqlite, just to run some queries on it. This is a two-step process:

1. convert json to csv

cat file.json | jq -r '.data | map([.field1, .field2, .field3] | @csv)| join("\n")' > file.csv

1a. add headers line into your csv file, i.e.

ex -sc '1i|field1,field2,field3' -cwq file.csv

2. import csv into sqlite

$ sqlite3 file.sqlite
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> .mode csv
sqlite> .import file.csv data
sqlite> .tables
data
sqlite> .schema dat
CREATE TABLE incidents(
  "field1" TEXT,
  "field2" TEXT,
  "field3" TEXT
);
sqlite> select count(*), field3 from data group by field3;
3,test3
1,test1
sqlite>