在安装了JSON1的SQLite中出现“没有这个函数：json_each”的错误

Question

我已经通过brew安装了带有JSON1的SQLite3：

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

版本：

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

运行查询时，某些函数可以正常工作，比如json_extract：

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

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

然而，当我尝试使用 json_each 或 json_tree 时，它会失败：

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

Error: no such function: json_each

Event表中的Body字段是一个有效的JSON字符串：

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

labels 值是一个数组。

我已经阅读了文档（并查看了 json_each 示例），在互联网上进行了搜索，但没有找到启用此功能的其他要求。

我做错了什么，或者说：如何从 json_each/json_tree 中获益？

- sgtdck

---

对于现在遇到这个问题的任何人，sqlite公式现在包括JSON1和FTS，因此您不需要使用标志。当前版本的sqlite（运行于MacBook Pro 2019）是3.32.3 2020-06-18。 - calvinf

Answer 1

据我所知，您不能在查询字段中使用json_each()和json_tree()，它们是表值函数。您只能像表格一样使用它们。请注意保留HTML标记。

- shA.t

Answer 2

问题在于json_each和json_tree是表值函数，这意味着它们只能用于获取已存在于内存中的虚拟表上的数据，而不能直接从数据库查询数据。

参见：SQLite的虚拟表机制

2.1.2 表值函数

包含隐藏列的虚拟表可以像一个表值函数一样在SELECT语句的FROM子句中使用。表值函数的参数成为虚拟表的隐藏列的约束条件。

当执行SELECT json_each(Body, '$.issue.fields.labels') ...时，sqlite3找不到与其定义相匹配的函数，因此会导致您看到的错误。

- Mike

Answer 3

sqlite.com上的json_each文档很简短。

以下内容可能有助于更好地理解如何使用json_each和其他json函数。

创建一个名为JsonTest的表。列Reply包含json值。

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

向表中插入一些记录

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"}]}';   

正如您所见

• 一些行仅包含Error，并且不是有效的JSON
• 其他行具有有效的JSON对象，属性body要么是empty的值，要么是类似以下结构的数组类型的值

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

如果列Reply始终具有有效的JSON值，例如'{"id": 5, "status": "nok" ...}，我们可以像这样查询JSON字段状态：

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

但是由于某些行/记录不是有效的json，因此可以使用类似于这样的子查询 (SELECT *, ...) as sq 结合 json_each(Reply, ...) 来过滤所有具有有效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;

在 sqlite-browser 中，这将返回此结果。

- surfmuggle