Ошибка: нет такой функции: json_each в SQLite с установленным JSON1

Я установил SQLite3 с JSON1 через brew:

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

Body поле в Event Таблица является допустимой строкой JSON:

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

И labels значение является массивом.

Я прочитал документацию (и посмотрел на примеры json_each), искал в Интернете, но не смог найти никаких дополнительных требований, чтобы включить это.

Что я делаю не так, или: как мне воспользоваться преимуществами json_each/json_tree?

Источник

3 ответа

Решение

Проблема в том, что json_each а также json_tree являются табличными функциями, что означает, что они могут использоваться только для извлечения данных из виртуальной таблицы, которая уже существует в памяти, а не для запроса данных непосредственно из базы данных.

См.: Механизм виртуальных таблиц SQLite

2.1.2. Табличные функции

Виртуальная таблица, содержащая скрытые столбцы, может использоваться как табличная функция в предложении FROM инструкции SELECT. Аргументы для табличной функции становятся ограничениями для столбцов HIDDEN виртуальной таблицы.

когда SELECT json_each(Body, '$.issue.fields.labels') ... sqlite3 не может найти функцию, которая соответствует определению SELECT и приводит к ошибке, которую вы видите.

Источник

AFAIK, вы не можете использовать json_each() а также json_tree() как поле в вашем запросе, они являются табличными функциями. Вы можете использовать их только как таблицы.

Источник

Документация по json_each на sqlite.com краткая.

Следующее может помочь лучше понять, как использовать json_each и другие функции json.

Создать таблицуJsonTest. Столбец содержит значения 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, ...)можно использовать для фильтрации всех записей с допустимым jsonWHERE 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-браузере это возвращает этот результат

Источник
Другие вопросы по тегам