https://sqlite.org/forum/forumpost/567caa57234b98e46b885bcf8...
https://sqlite.org/forum/forumpost/f4b44e09cea20851014420a8b...
sqlite-utils is also a helpful project for various utilities : https://sqlite.org/forum/forumpost/ff5b6198eb6e794d2f774ce06...
I guess you are trying to do below from your comment.
$ echo '["Kate", "John", "Jim"]' > names.json
$ ./sqlite3
SQLite version 3.38.0 2022-02-22 18:58:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table users(id integer primary key, name text);
sqlite> insert into users(name) select value from json_each(readfile('names.json'));
sqlite> select * from users;
1|Kate
2|John
3|Jim select value from json_each(readfile('names.json'))
It works with YAML, TOML, JSON, jsonlines, CSV, and regular shell command output. You can import from any data format and convert to any other data format and even in line SQL relational look ups too.
Since SQL inlining literally just imports the data into an in memory sqlite3 database it means you can do your JSON import into sqlite3 using this shell. And in fact I did literally just this last month when using a cloud service restful API which returned two different JSON docs that needed to restructured into two different tables and then a relational query run between them.
https://github.com/sqlite/sqlite/blob/a0318fd7b4fbedbce74f13...
https://www.postgresql.org/docs/current/datatype-json.html
> The json and jsonb data types accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.
> SQLite does not (currently) support a binary encoding of JSON. Experiments have been unable to find a binary encoding that is significantly smaller or faster than a plain text encoding. (The present implementation parses JSON text at over 1 GB/s.) All JSON functions currently throw an error if any of their arguments are BLOBs because BLOBs are reserved for a future enhancement in which BLOBs will store the binary encoding for JSON.
Wouldn’t a jsonb-type storage require a new storage mode for sqlite, which would be a major architectural change?
JSONB derives (at least logically and historically) from hstore, so postgres had a history of structured type stores. Not so for sqlite.
If you look at the sample data section, you’ll see an example of the data they’re using.
It’s mainly to parse and work with json data that’s stored.
I asked them if they'd considered the most recent syntax added by PostgreSQL - "where jsoncol["prop"] = 'x'" - but sadly that was incompatible with other existing SQLite syntax.