5 ms·
New JSON query operators in SQLite 3.38.0
- stonecharioteer 5y agoBrilliant article as always.
- xtreak29 5y agoThanks
- slively 5y agoWell that’s exciting, congratulations to the SQLite team. The new type checking and json support makes SQLite a pretty amazing option for even more use cases.
- throw_m239339 5y agoNice, I love using JSON query with SQLite, greatly simplifies object hydration.
- qwertox 5y agoI've been using it successfully for years on Android with sqlite-android [0] and the flexibility it has given me was quite a relief. It's great to see that it is now included by default. [0] https://github.com/requery/sqlite-android https://github.com/requery/sqlite-android
- fegu 5y agoI would love for sqlite to add import of JSON. They have great CSV import and export. If we could easily import, say, JSON arrays to a single column table, it would make JSON conversion to/from CSV easier. Especially since jq is slow and unmaintained.
- porker 5y agoMe too.
- xtreak29 5y agoThere are couple of threads related to json import but a default mode will be helpful as there is already .mode json for output and import support will be great. https://sqlite.org/forum/forumpost/567caa57234b98e46b885bcf86a22526bffaf603f777e25e22b720de0e9a89c9 https://sqlite.org/forum/forumpost/567caa57234b98e46b885bcf8... https://sqlite.org/forum/forumpost/f4b44e09cea20851014420a8b91cea8254c14951635307d964ab6c9fa283c982 https://sqlite.org/forum/forumpost/f4b44e09cea20851014420a8b... sqlite-utils is also a helpful project for various utilities : https://sqlite.org/forum/forumpost/ff5b6198eb6e794d2f774ce06b2ae7aab3163c52ad5ba7f093494f008a8236d9 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
- e12e 5y agoIs this: select value from json_each(readfile('names.json')) Built in to (standard) sqlite? It's a little unclear from your comment?
- xtreak29 5y agoIt seems readfile is from an extension but present in cli. json_each is present in standard sqlite core from 3.38.0 as json1 extension is also now part of core. json_each : https://www.sqlite.org/json1.html#jeach https://www.sqlite.org/json1.html#jeach readfile : https://sqlite.org/cli.html#file_i_o_functions https://sqlite.org/cli.html#file_i_o_functions > Note that the readfile(X) and writefile(X,Y) functions are extension functions and are not built into the core SQLite library. These routines are available as a loadable extension in the ext/misc/fileio.c source file in the SQLite source code repositories.
- josalhor 5y agoHow efficient is this? Is JSON stored as a blob and parsed and filtered on each query?
- rovr138 5y agoYes. 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.
- xtreak29 5y agoIn case of PostgreSQL there is json and jsonb. For SQLite, hexdump of the database shows text representation and seems to be stored like json than jsonb. I am not aware of the full design and source code but it seems some functions parse and cache the JSON representation. https://github.com/sqlite/sqlite/blob/a0318fd7b4fbedbce74f133fb0f84ff4a19ea075/src/json.c#L1005 https://github.com/sqlite/sqlite/blob/a0318fd7b4fbedbce74f13... https://www.postgresql.org/docs/current/datatype-json.html 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.
- masklinn 5y ago> In case of PostgreSQL there is json and jsonb. For SQLite, hexdump of the database shows text representation and seems to be stored like json than jsonb. I am not aware of the full design and source code but it seems some functions parse and cache the JSON representation. 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.
- 5y ago
- gigatexal 5y agoStill one of the most useful things I’ve ever come across. Glad to see SQLite development continuing and being amazing.
- simonw 5y agoI did some research recently to figure out exactly how the path syntax used by the various SQLite JSON functions and operators works: https://til.simonwillison.net/sqlite/json-extract-path https://til.simonwillison.net/sqlite/json-extract-path
- xtreak29 5y agoThanks, your sqlite-utils and other libraries have been very helpful.
- jrochkind1 5y agoThese look like they match what postgres uses? Not sure if postgres invented it, or got it from somewhere else? Time for some specs/standards for JSON operators in SQL?
- simonw 5y agoYeah, the SQLite team cited PostgreSQL as the inspiration here. 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.
- xtreak29 5y agoDiscussion: https://sqlite.org/forum/forumpost/d61880668db1a8f4 https://sqlite.org/forum/forumpost/d61880668db1a8f4
- jrochkind1 5y agoOne comment there points out some differences with the operators in pg, which I don't entirely follow honestly. https://sqlite.org/forum/forumpost/a0d681bb2432eebc https://sqlite.org/forum/forumpost/a0d681bb2432eebc
- bokwoon 5y agohi I was the one who made that comment. The JSON incompatibilities I was referring to at that time was for an older version of the JSON proposal. The current version of the JSON operator in SQLite mimics Postgres (and MySQL) perfectly and I'm very happy about that. In the older version of the proposal, -> was identical to ->> except -> returned NULL on malformed JSON (while ->> raised an error). Both -> and ->> would automatically convert a JSON-encoded SQL string '"like this"' into an SQL string 'like this'. This is not how the -> operator behaves in Postgres and MySQL, and my examples were simply trying to point out that incompatibility.
- 5y ago
- dekhn 5y agoThis is interesting; I didn't real it carefully enough to know for sure, but one of the big challenges in moving from SQL to semistructed data like JSON is being able to do queries into deep fields (combined with repeating fields).
- NelsonMinar 5y agoThis release of sqlite also has JSON support on by default, so JSON is much more likely in whatever sqlite build happens to be in the software you're using.