SQLite Forum

Insert ("slurp" ?) JSON documents
Login

Insert ("slurp" ?) JSON documents

(1) By anonymous on 2020-10-05 17:18:13 [link] [source]

I have a table like raw_data(data json). I'm trying to just dump (via unix pipes) some json documents collected from the web. Something like curl "HTTP://...json" jq . sqlite3 my.db . The problem is that the .import commands usually works for tabular data. Handling quotes and whitespace via json is driving me nuts.

Any simple way I can achieve this? To make things simple, each pipe output inserts only one row.

(2) By Simon Willison (simonw) on 2020-10-06 01:10:00 in reply to 1 [link] [source]

My sqlite-utils CLI tool can do exactly this:

echo '[
    {
        "id": 1,
        "name": "Cleo",
        "age": 4
    },
    {
        "id": 2,
        "name": "Pancakes",
        "age": 2
    },
    {
        "id": 3,
        "name": "Toby",
        "age": 6
    }
]' | sqlite-utils insert dogs.db dogs - --pk=id

Having run this a new database file called dogs.db will be created with the following schema:

$ sqlite3 dogs.db .schema
CREATE TABLE [dogs] (
   [id] INTEGER PRIMARY KEY,
   [name] TEXT,
   [age] INTEGER
);

(3) By anonymous on 2020-10-07 23:36:48 in reply to 1 [source]

It is unclear what you are trying to do. There is a JSON1 extension for dealing with JSON data in SQLite.

If simonw is correct, then while the sqlite-utils CLI tool may do it like mentioned, it is also possible to do using the JSON1 extension, as follows:

insert into dogs(id,name,age) select json_extract(value,'$.id'),json_extract(value,'$.name'),json_extract(value,'$.age') from json_each(readfile('dogs.json'));

(Although using sqlite-utils is probably more convenient.)

If you are just trying to put each raw JSON file into a separate row of the table, then you do not need to parse JSON at all; you can just use the readfile function (which is part of the standard SQLite command shell).

If you are trying to do something else, read the documentation for the JSON1 extension, and see if it explains it.

(4) By Simon Willison (simonw) on 2020-10-08 03:06:50 in reply to 3 [link] [source]

I did not know about that readfile function - this is a really clever solution.