[SOLVED] Error: no such function: json_each in SQLite with JSON1 installed

Error: no such function: json_each in SQLite with JSON1 installed

I've installed SQLite3 with JSON1 through brew:

brew install sqlite3 --with-json1 --with-fts5

Version:

3.15.2 2016-11-28 19:13:37 bbd85d235f7037c6a033a9690534391ffeacecc8

When running a query, some functions work fine, such as json_extract:

sqlite> SELECT json_extract(Body, '$.issue.fields.labels') FROM Event WHERE json_extract(Body, '$.issue.fields.labels') != '[]';

["foo","bar","baz"]

However, when I try to use json_each or json_tree, it fails:

sqlite> SELECT json_each(Body, '$.issue.fields.labels') FROM Event WHERE json_extract(Body, '$.issue.fields.labels') != '[]';

Error: no such function: json_each

The Body field in the Event table is a valid JSON string:

{"issue":{"fields":{"labels": ["foo","bar","baz"]}}}

And the labels value is an array.

I've read the documentation (and looked at the json_each examples), searched the interwebs, but couldn't find any additional requirements to enable this.

What am I doing wrong, or: how do I reap the benefits from json_each/json_tree?

Solution

The problem is that json_each and json_tree are table-valued functions which means that they can only be used to fetch data on a virtual table that already exists in memory, not to query data directly from the database.

See: The Virtual Table Mechanism Of SQLite

2.1.2. Table-valued functions

A virtual table that contains hidden columns can be used like a table-valued function in the FROM clause of a SELECT statement. The arguments to the table-valued function become constraints on the HIDDEN columns of the virtual table.

When SELECT json_each(Body, '$.issue.fields.labels') ... sqlite3 can't find a function that matches with its definition of SELECT and results in the error you see.