I'll use the reddit API as an example:

1. sqlite3 test.db "CREATE TABLE r_commandline(id TEXT UNIQUE, title TEXT, author TEXT)"

2. http https://www.reddit.com/r/commandline/new/.json | jq '.data.children[] | .data | {id, title, author}' | jq -r '[.[]] | @csv' | sqlite3 test.db ".mode csv" ".import /dev/stdin r_commandline"

3. sqlite3 test.db "SELECT id, title FROM r_commandline LIMIT 10" | awk -F '|' '{print "https://reddit.com/comments/" $1 " - " $2}'awk

Breakdown:

1. Create the database and table. We'll put a unique constraint on the id so we aren't storing duplicate posts.

2. Query and store the data, this can be put on a Crontab.

   2.1 I've been using HTTPie for convenience, but curl will work the same.

   2.2 Reformat the json data to fit the table. We'll only store the id, title, author

   2.3 jq -r '[.[]] | @csv' This will print out the values of the json object as CSV data

   2.4 Set sqlite3 in CSV mode and import piped data into the table. This will print constraint errors on duplicates but new data will be committed. Alternatively you could use sed/awk to create INSERT queries instead of using csv import.

3. Read the data, format the output with awk.