SQLITE: JSON1 Extension

[jzelinskie]

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:

    SELECT DISTINCT user.name
    FROM user, json_each(user.phone)
    WHERE json_each.value LIKE '704-%';

It's pretty neat considering they rolled it all on their own: https://www.sqlite.org/src/artifact/552a7d730863419e

PS: If you haven't looked at the SQLite source before, check out their tests.

[seepel]

On the other hand the same version supports indices on expressions that could be used to create an index on json values. Not exactly the same but could be used for similar use cases.

[brotherjerky]

Anything specific about their tests? I found this doc: http://www.sqlite.org/testing.html

[throwawaygehehe]

I'm pretty sure one can create indices based on JSON expressions (see, for example, http://sqlite.1065341.n5.nabble.com/Partial-indexes-on-JSON-...). I don't know how it compares to Postgres' JSON support.

[hvidgaard]

Kinda like SQL Server. Postgres has the superiour implementation for now.

[astrostl]

> It should be made clear that this functionality is not equivalent to the Postgres JSON support

I found that pretty clear by way of it being SQLite :P

[mgalka]

How does the speed compare to MondoDB?

[Lxr]

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?

[singingfish]

when you have arbitrary semi-structured data of limited scope to store. For example the stuff I was working on today is pluggable payment infrastructure. The vendor response is stored json (comes down the wire as xml or json, json is easier, I refuse to anticipate the structure of the data for future providers but I want the whole data returned for debug purposes), and the extra data it requires for transaction resolution is also json. Again I have no idea what this will look like for future payment providers, and this data will not result in consequences for other bits of the database.

[vhost-]

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.

[m_fayer]

I thought everyone loved SQLite! It's tiny, no-fuss but full-featured, performs well, and works great as a data-interchange format. I use it in all my simple ad-hoc personal apps, and where would mobile be without it?

[sbuttgereit]

I think there could be some instances where people suggest or build stuff with SQLITE when a more robust database solution is called for. Or they start test/develop with SQLITE when the final target is, say, PostgreSQL and delay too long the time they get tests and rigor in place on the real database (or avoid doing so outright). I don't know that you fall into any of those camps (I don't know you after all), but if you did I could understand why you'd find yourself on the wrong side of opinion.

However, SQLITE is great for ad hoc data manipulation as you say, and there are application niches, particularly on the client side where it can be great. By itself, everything I know about it is that is an excellent piece of software and the tests are practically a case study in rigor (again, from what I've heard).... so how could you not love that?

[laumars]

I'm a recent convert to sqlite - started using it in CLI Go apps as a more robust way of handling large data sets than building my own bespoke in memory databases. It's not often I come across software that impresses and amazes me as much as sqlite does. It's tiny, ridiculously easy to implement, writes 1 flat file, uses comparatively little memory, and is /fast/. I don't even know how they managed to make it so fast given all the other constraints.

Seriously impressive database.

[cyberferret]

I've been a fan of SQLite for nearly two decades now! use it all the time on my Win32 apps for recording things like local log files and other things that don't require multi user access. I recall even building a simple local network full text search engine for legal documents for a law firm using SQLite with their FTS3 extension once.

Built a few iOS app with it as the data store too. Love the 'zero configuration' install. From memory I've even used DOS batch files to manipulate data on SQLite...

[wybiral]

I'm a fan of sqlite for similar reasons.

I also use it frequently to do some data massaging before loading into a larger database since it has a standard Python module and queries are quick and easy.

[c4pt0r]

I love sqlite, and I also love MySQL/MongoDB/PG/HBase/TiDB...just use the right tool for different job.

[kobeya]

Most people I interact with love sqlite.

[nbevans]

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.

[thinknlive]

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.

[mablap]

Oooh! I just arrived at work and things are calm, this will be interesting reading! We use sqlite for mostly every tool we develop.

[adsharma]

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.

Plug: https://github.com/facebookincubator/iterlib

[vdm]

Thank you for plugging iterlib here, I hadn't heard of it.

Have you heard of storehaus? https://github.com/twitter/storehaus

In terms of the fundamental abstraction offered, it seems comparable to iterlib to me, but I'd love to hear your opinion.

(See also: https://upscaledb.com/)

[ilitirit]

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?

[giancarlostoro]

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.

[nattaylor]

>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.)

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?

[haldean]

Looks like you can't index based on JSON in SQLite, so they might be optimizing for different metrics?

[burrows]

Here are instructions for building the extension as a shared library on OS X https://burrows.svbtle.com/build-sqlite-json1-extension-as-s....

I wasn't able to get a working build on Windows.

[marvel_boy]

Thanks for the procedure. Exactly what I needed !

[voltagex_]

What happened on Windows?

[tenken]

Is there a Ubuntu PPA or docker image of SQLite + JSON extension enabled?

I can't get the compiled json1.so to load on Ubuntu 14.04 lts with stock SQLite.

[onli]

I think the version on 14.04 is too old. I found the statement you need 3.9 for that (is that somewhere documented properly?).

For what is worth, the sqlite3 version in Ubuntu 16.04 has that json1-extension loaded by default:

    sqlite> CREATE TABLE user (name TEXT, phone TEXT);
    sqlite> INSERT INTO user VALUES("tester", '{"phone":"704-56"}');
    sqlite>  SELECT DISTINCT user.name FROM user, json_each(user.phone) WHERE json_each.value LIKE '704-%';
    tester

I'm now even more happy I upgraded my server to that version yesterday, the server for that project was like yours on 14.04. It was even also because of sqlite, I wanted to have a version that supports the WITH clause. Upgrading to 16.04 (actually, I made a fresh install and moved the data over) seemed like the easiest way to get that.

[GrumpyNl]

How does this perform on larger tables?

[zxv]

Is there any plan to support jsonb (or similar) which would speed processing by eliminating the need to reparse?

[mayli]

The embedded version of mongodb?

[Buttons840]

> The json_object() function currently allows duplicate labels without complaint, though this might change in a future enhancement.

Just like Mongo. I was suprised to learn that Mongo can actually store an object with two identical keys. Most drivers will prevent you from doing so, and will fail to display the record fully if it does happen, but it is possible.

[andrewstuart2]

More like embedded postgres.