Skip to main content

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. 

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 '' \
--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