How to Use sqlite-utils to Create Tables from JSON Input

There are many way to query JSON data. This article focuses on one of those possible ways using few developer utility tools. 

• jqplay.org : https://jqplay.org/s/nwxNZ7LOY6
• sqlite3: https://www.sqlite.org/cli.html
• sqlite-utils: https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-json-data

You can call an HTTP API as below. Use jq to transform the return content as you need. Then pipe it through sqlite-utils to perform upsert. Following code snippet simply creates a "test" table with primary key being the id attribute. 

curl --location --request POST 'https://seyfi.net/searches?size=1000&page=0' \
--header 'Authorization: Bearer XXXXXX' \
--header 'Content-Type: application/json' \
--data-raw '{"date":"2019-01-01T00:00:00.000Z"}' | jq '.content' | sqlite-utils upsert /tmp/test.db content - --pk=id

Once this executes, you can then filter this and export easily using Sqlite3 as follows.

>sqlite3 /tmp/test.db
sqlite> .headers on
sqlite> .mode csv
sqlite> .output content.csv
sqlite> SELECT column1,
   ...>        column2,
   ...>        column3
   ...>   FROM content;
sqlite> .quit