SQLite extension JSON1

JSON1 was an SQLite extension providing functions for manipulating JSON. JSON data itself is not special-cased in SQLite when JSON1 is enabled; it is simply regarded as text. It's fast.

As of SQLite 3.38.0 (2022-02-22), JSON1's old functions have all been moved into the SQLite core, and should always be present unless they were explicitly disabled at build time with -DSQLITE_OMIT_JSON.

Installation (SQLite 3.37.x and earlier)

Run the following POSIX shell commands to compile and install the SQLite Tcl extension with JSON1. If the installation is a success, be sure to remove the version installed with your OS' package manager to prevent Tcl from loading it instead. You can do this, e.g., with the command sudo apt remove libsqlite3-tcl on Debian/Ubuntu.

#! /bin/sh
set -e
version=3250100
wget "https://sqlite.org/2018/sqlite-autoconf-$version.tar.gz"
tar zxvf "sqlite-autoconf-$version.tar.gz"
cd "sqlite-autoconf-$version/tea"
CFLAGS='-DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1' ./configure
make
sudo make install

oldlaptop - 2021-11-09 05:55:17

Probably good to check first before performing a manual installation on Linux/BSD/etc. - some (including recent Debian as of this writing) build their sqlite with JSON1 enabled by default [and, of course, few will go to the trouble of explicitly disabling JSON support in 3.38.0 and newer]. (Do note however that it's possible for the sqlite3 shell program to have a different set of extensions enabled than the C or Tcl libraries!)


oldlaptop - 2021-11-09 06:15:59

I've found JSON1's table valued functions (JSON_EACH() and JSON_TREE()) in particular to be a very convenient and natural way (if calling out to SQL queries can be called natural...) to iterate over JSON structures from Tcl scripts. All the JSON type and structure information is there if you need it, but you can also flatten an entire JSON object tree to a list of string-valued atoms in one swoop if that's what makes more sense.