SQLite extension JSON1

SQLite includes quite rich support for handling JSON values . In older versions of SQLite, JSON support was part of an optional extension called JSON1. As of SQLite 3.38.0 (2022-02-22), however, 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. SQLite 3.38.0 also added operators for working with JSON values.

JSON values aren't special-cased in SQLite; they're simply stored as ordinary TEXT values. (The parser is quite fast.) Version 3.45.0 added support for working with "JSONB" values, which are a pre-parsed representation stored as ordinary BLOB values.

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
wget "https://sqlite.org/2018/sqlite-autoconf-$version.tar.gz"
tar zxvf "sqlite-autoconf-$version.tar.gz"
cd "sqlite-autoconf-$version/tea"
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.