Nelson's log

JSON1, a SQLite extension

A couple months back I expressed a desire for a SQLite-for-JSON system. And someone’s done it! I got here from this Hacker News discussion about a different system.


JSON1 is a draft SQLite extension for JSON. Here’s notes on how to use it in Python, including a demo notebook. Still some rough edges, but it’s promising.

JSON is stored as type text. No binary encoding, the docs say the improvement isn’t worth the trouble. JSON arrays are used as a container type in parts of the query language, for things that return N JSON blobs. There’s also some SQLite virtual tables that are created by advanced query methods.

There’s the basic functions you’d expect for manipulating JSON, mostly setting and changing properties on a JSON object.

For basic query, json_extract() is the basic function that pulls out pieces of a document based on a query path. (Ie: $.title means “give me the title property”, $.authors[2] means “give me the 3rd element of the array that is the value of the authors property”)

For advanced query, json_each() and json_tree() turn a single JSON blob into a virtual table you can then query. The table has a schema with one JSON column per property queried, as well as some other data related to the query like integer IDs and the path. json_each() only queries the top level properties, json_tree() is a recursive search.

I like it, it looks really promising. Maybe not quite ready for civilian use yet, but soon? The discussion a month ago advises we “check back soon”.

My one mild concern is that JSON data tends to be really big. Like I have 1000 LoL matches in a SQLite database, each one a 200kb JSON blob. And no surprise, it’s a 200MB file! That starts to get awkward for backups, etc, and is kinda dumb since the JSON is immutable. It’d be better to store them as read-only files but that’s not compatible with SQLite’s design.


JSONlite is the topic of the HN post that let me to JSON1. It’s a valiant effort, a set of bash scripts to store JSON as files and retrieve them with UUIDs. It’s not a bad little wrapper but it’s very simple. And no query interface, which to me is the interesting part, although one could probably just use jq on the backend store.


UnQLite also came up, in this description. “UnQLite is to MongoDB what SQLite is to Postgres”, which sounds good to me! It uses Jx9 for query. I didn’t look any further; it seems promising, but my general rule with tech like this is wait for critical mass before investing any time.