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

Question

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?

Evaginate

For anyone coming across this now, the sqlite formula now includes JSON1 and FTS so you don't need the flags. Current version of sqlite in there (running MacBook Pro 2019) is 3.32.3 2020-06-18. – Thomajan

Answer 1

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.

Chatterer

Answer 2

AFAIK, You can't use json_each() and json_tree() as a field in your query, they are table-valued functions. You can only use them like tables.

Loo

Answer 3

The docs on json_each on sqlite.com are brief.

The following may help to better understand how to use json_each and other json functions.

Create a table JsonTest. The column Reply contains json values.

CREATE TABLE "JsonTest" (
    "Id"    INTEGER NOT NULL UNIQUE,
    "Reply" TEXT,
    PRIMARY KEY("Id" AUTOINCREMENT)
)

Insert some records into the table

INSERT INTO JsonTest (Reply) 
SELECT 
'{"id": 2, "status": "ok", "body": [{"Ftr": "Gears", "Val": "10"},{"Ftr": "Brake", "Val": "Disc-Brake"}]}' as REPLY
UNION SELECT '{"id": 4, "status": "ok",  "body": [{"Ftr": "Gears", "Val": "12"},{"Feature": "Brake", "Val": "Disc-Brake"}]}'
UNION SELECT 'Error'     
UNION SELECT '{"id": 1, "status": "nok", "body": "empty"}'   
UNION SELECT 'Error'     
UNION SELECT '{"id": 5, "status": "nok", "body": "empty"}'
UNION SELECT '{"id": 6, "status": "ok",  "body": [{"Ftr": "Gears", "Val": "21"},{"Ftr": "Brake", "Val": "V-Brake"}]}'
UNION SELECT '{"id": 8, "status": "ok",  "body": [{"Ftr": "Gears", "Val": "18"},{"Ftr": "Brake", "Val": "V-Brake"}]}';   

As you can see the

• some rows only contain Error and are not valid json
• other rows have a valid json object the property body has either a value empty or a value of type array like this

[
  {"Ftr": "Gears", "Val": "21"},
  {"Ftr": "Brake","Val": "V-Brake"}
]

If the column Reply would always have a valid json value like this '{"id": 5, "status": "nok" ...} we could query for json field status like this:

SELECT JsonTest.id, jsonEach.Value 
    FROM JsonTest,json_each(JsonTest.Reply, '$.status') as jsonEach
    WHERE JsonTest.Reply not like 'Error'; 

But since some rows / records are not valid json a subquery like this (SELECT *, ...) as sq combined with json_each(Reply, ...) can be used to filter all records with valid json WHERE JSON_VALID(Reply).

SELECT subquery.RecordId, subquery.Reply_id, jsonEach.value 
FROM 
(
  SELECT *, Id as RecordId, json_extract(Reply, '$.id') as Reply_id 
  FROM JsonTest 
  WHERE JSON_VALID(Reply)  -- filter to get only valid json rows
  AND json_extract(Reply, '$.status') like "ok" 
  AND json_extract(Reply, '$.body[1].Val') like "V-Brake"
) as subquery, json_each(Reply, '$.body') as jsonEach;

In sqlite-browser this returns this result

Finisterre