It should be made clear that this functionality is not equivalent to the Postgres JSON support where you can create indexes based on the structure of the JSON. This is an extension to the query language that adds support for encoding/decoding within the context of a query:<p><pre><code> SELECT DISTINCT user.name
FROM user, json_each(user.phone)
WHERE json_each.value LIKE '704-%';
</code></pre>
It's pretty neat considering they rolled it all on their own: <a href="https://www.sqlite.org/src/artifact/552a7d730863419e" rel="nofollow">https://www.sqlite.org/src/artifact/552a7d730863419e</a><p>PS: If you haven't looked at the SQLite source before, check out their tests.
Wait, what? Isn't storing JSON data as text in a relational DB against all kinds of normalisation rules? Under what circumstances should one do this?
I always seem to be the black sheep in a group of people when I say that I love sqlite. It's seriously so handy. If I need to aggregate or parse big CSV sheets, I just toss them in a sqlite database and run actual SQL queries against the data and it makes the process much faster and I can give someone their data in relational form on a thumbdrive or in a tar.gz.
SQLite is a great database for microservices and other minimalist architectures. You'll never get a "TCP socket failed/timeout/reset" from SQLite - that's for sure.
sqlite 'all the things!'. Seriously. One of the best tools ever. So many data, and related performance challenges, in almost any app can be solved efficiently with this (for what it does) tiny little library.
Not clear if you can compose these functions. Flatbuffers over rocksdb should be considered an alternative. You can then use iterlib to do similar things.<p>Plug: <a href="https://github.com/facebookincubator/iterlib" rel="nofollow">https://github.com/facebookincubator/iterlib</a>
I use this for a custom JSON query tool and browser I wrote for our company (the C# client can load extensions). It's been available for a while. Is this post to spread awareness or have they added something to new to the extension?
This is very interesting, I wonder what drove this extension and how things will shape with SQLite. I always loved that in Python I can 'just use' SQLite quite easily.
>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 300 MB/s.)<p>I understand that JSONB in Postgres is useful primarily for sorts and indexes. Does SQLite work around this somehow, or is that just not included in their experiments?
Here are instructions for building the extension as a shared library on OS X <a href="https://burrows.svbtle.com/build-sqlite-json1-extension-as-shared-library-on-os-x" rel="nofollow">https://burrows.svbtle.com/build-sqlite-json1-extension-as-s...</a>.<p>I wasn't able to get a working build on Windows.
Is there a Ubuntu PPA or docker image of SQLite + JSON extension enabled?<p>I can't get the compiled json1.so to load on Ubuntu 14.04 lts with stock SQLite.