SQLite extension JSON1

JSON1 is an SQLite extension that provides 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.

Installation

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