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?

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.

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.

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