1. Обзор

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

Есть 15 скалярных функций и операторов:

1. json(json)
2. json_array(value1,value2,...)
3. json_array_length(json)
   json_array_length(json,path)
4. json_error_position(json)
5. json_extract(json,path,...)
6. json -> path
7. json ->> path
8. json_insert(json,path,value,...)
9. json_object(label1,value1,...)
10. json_patch(json1,json2)
11. json_remove(json,path,...)
12. json_replace(json,path,value,...)
13. json_set(json,path,value,...)
14. json_type(json)
    json_type(json,path)
15. json_valid(json)
16. json_quote(value)

Есть две совокупных функции SQL:

17. json_group_array(value)
18. json_group_object(label,value)

Есть две табличных функции:

19. json_each(json)
    json_each(json,path)
20. json_tree(json)
    json_tree(json,path)

2. Компиляция с поддержкой JSON

Функции JSON и операторы встроены в SQLite по умолчанию с версии SQLite version 3.38.0 (2022-02-22). Они могут быть опущены, добавив выбор времени компиляции -DSQLITE_OMIT_JSON. До версии 3.38.0 функции JSON были расширением, которое будет включено только если выбор времени компиляции -DSQLITE_ENABLE_JSON1 указан.

3. Обзор интерфейса

SQLite хранит JSON как обычный текст. Ограничения на совместимость означают, что SQLite в состоянии сохранить только значения, которые являются NULL, целыми числами, числами с плавающей запятой, текстом и BLOB. Невозможно добавить шестой тип "JSON".

SQLite (в настоящее время) не поддерживает двоичное кодирование JSON. Существующее внедрение разбирает текст JSON на уровне более чем 250 МБ/с. Все функции JSON в настоящее время бросают ошибку, если какой-либо из их аргументов BLOB, потому что BLOB резервируются для будущего улучшения, в котором BLOB сохранят, используя двоичное кодирование для JSON.

3.1. Аргументы JSON

Для функций, которые принимают JSON как их первый аргумент, тот аргумент может быть объектом JSON, множеством, числом, последовательностью или пустым указателем. Числовые значения SQLite и NULL интерпретируются как числа JSON и null, соответственно. Текстовые значения SQLite могут быть поняты как объекты JSON, множества или последовательности. Если текстовое значение SQLite, которое не является правильно построенным объектом JSON, множеством или последовательностью, будет передано в функцию JSON, та функция будет обычно бросать ошибку. Исключения к этому правилу: json_valid(), json_quote() и json_error_position().

Этот установленный порядок понимает весь синтаксис rfc-7159 JSON и расширение JSON5. Текст JSON, произведенный этим установленным порядком всегда строго соответствует каноническому определению JSON и не содержит JSON5 или других расширений. Способность читать и понять JSON5 была добавлена в версии 3.42.0 (2023-05-16). Предыдущие версии SQLite прочитали бы только канонический JSON.

3.2. Аргументы PATH

Для функций, которые принимают параметры PATH, этот PATH должен быть правильно построен, иначе функция бросит ошибку. Правильно построенный PATH это текстовое значение, которое начинается точно с одного символа '$', сопровождаемого нолем или большим количеством ".objectlabel" или "[arrayindex]".

arrayindex это обычно неотрицательное целое число N. В этом случае отобранный элемент матрицы является N-м элементом множества, начинающегося с ноля слева. arrayindex может также иметь форму "#-N", в этом случае, отобранный элемент является N-м справа. Последний элемент множества "#-1". Думайте о символах "#" как о "числе элементов во множестве". Тогда выражение "#-1" оценивается к целому числу, которое соответствует последнему входу во множестве. Для индекса массива иногда полезно быть просто символом #, например прилагая значение к существующему множеству JSON:

• json_set('[0,1,2]','$[#]','new') → '[0,1,2,"new"]'

3.3. Аргументы VALUE

Для функций, которые принимают аргументы "value" (также показаны как "value1" и "value2"), теми аргументами, как обычно понимают, являются литеральные строки, которые указаны и становятся значениями последовательности JSON в результате. Даже если последовательности входного значения похожи на правильно построенный JSON, они все еще интерпретируются как литеральные строки в результате.

Однако, если аргумент value прибывает непосредственно из результата другой функции JSON или от оператора -> (но не оператора ->>), то аргументом, как понимают, является фактический JSON, и полный JSON вставляется, а не указанная последовательность.

Например, в следующем вызове json_object() аргумент value похож на правильно построенное множество JSON. Однако, потому что это просто обычный код на SQL, это интерпретируется как литеральная строка и добавляется к результату как указанная последовательность:

• json_object('ex','[52,3.14159]') → '{"ex":"[52,3.14159]"}'
• json_object('ex',('52,3.14159]'->>'$')) → '{"ex":"[52,3.14159]"}'

Но если value во внешнем вызове json_object() это результат другой функции JSON, например, json() или json_array(), значение, как понимают, является фактическим JSON и вставляется как таковой:

• json_object('ex',json('[52,3.14159]')) → '{"ex":[52,3.14159]}'
• json_object('ex',json_array(52,3.14159)) → '{"ex":[52,3.14159]}'
• json_object('ex','[52,3.14159]'->'$') → '{"ex":[52,3.14159]}'

Аргументы "json" всегда интерпретируются как JSON независимо от того, откуда для того аргумента поступает значение. Но аргументы "value" интерпретируются как JSON только если те аргументы прибывают непосредственно из другой функции JSON или оператора ->.

В аргументах значенияJSON, интерпретируемых как последовательности JSON, управляющие последовательности Unicode не рассматривают как эквивалентные знакам или экранированным управляющим символам, представленным выраженной кодовой точкой Unicode. Такие escape-последовательности не переводят или рассматривают особо, их рассматривают как простой текст функции JSON SQLITE.

3.4. Совместимость

Текущее внедрение этой библиотеки JSON использует анализатор рекурсивного спуска. Чтобы избегать использования избыточного стекового пространства, любой вход JSON, у которого есть больше чем 1000 уровней вложения, считают недействительным. Ограничения на глубину вложения позволены для совместимых внедрений JSON RFC-7159 section 9.

3.5. Расширения JSON5

Начиная с version 3.42.0 (2023-05-16), это будет читать и интерпретировать входной текст JSON, который включает расширения JSON5. Однако, текст JSON, произведенный этим установленным порядком, будет всегда строго соответствовать каноническому определению JSON.

Вот резюме расширений JSON5 (адаптировано от JSON5 specification):

• Ключи объекта могут быть неэкранированными идентификаторами.
• У объектов может быть единственная запятая в конце.
• У множеств может быть единственная запятая в конце.
• Последовательности могут быть однократно цитированы.
• Последовательности могут охватить много строк, экранируя символы новой строки.
• Последовательности могут включать новые символы цитирования.
• Числа могут быть шестнадцатеричными.
• У чисел могут быть десятичные точки в конце или начале.
• Числа могут быть "Infinity", "-Infinity" и "NaN".
• Числа могут начаться с явного знака +.
• Комментарии позволены в одну (//...) или несколько строк (/*...*/).
• Дополнительные пробельные символы позволены.

Чтобы преобразовать последовательность X из JSON5 в канонический JSON, вызовите "json(X)". Вывод "json()" будет каноническим JSON независимо от любых расширений JSON5, которые присутствуют во входе. Для обратной совместимости json_valid(X) продолжает сообщать false для входов, которые не являются каноническим JSON, даже если вход JSON5, который функция в состоянии понять. Чтобы определить, является ли входная строка действительным JSON5, используйте выражение: "json_error_position(X)==0".

Этот установленный порядок понимает все JSON5, плюс немного больше. SQLite расширяет синтаксис JSON5 этими двумя способами:

1. Строгий JSON5 требует, чтобы неупомянутые ключи объекта были ECMAScript 5.1 IdentifierNames. Но большие unicode-таблицы и много кода требуются, чтобы определить, является ли ключ ECMAScript 5.1 IdentifierName. Поэтому SQLite позволяет ключам объекта включать любые символы unicode больше U+007f, которые не являются пробельными символами. Это ослабленное определение "identifier" значительно упрощает внедрение и позволяет анализатору JSON быть меньше и работать быстрее.

2. JSON5 позволяет бесконечностям с плавающей запятой быть выраженными как "Infinity", "-Infinity" или "+Infinity", если начальная "I" в верхнем регистре, а остальные буквы в нижнем. SQLite также позволяет сокращение "Inf" вместо "Infinity" и это позволяет ключевым словам появляться в любой комбинации прописных и строчных букв. Точно так же JSON5 позволяет "NaN" для not-a-number. SQLite расширяет это, чтобы также позволить "QNaN" и "SNaN" в любой комбинации прописных и строчных букв. Обратите внимание на то, что SQLite интерпретирует NaN, QNaN и SNaN просто как альтернативное правописание для "null". Это расширение было добавлено, потому что (нам говорят), существует много JSON в дикой природе, которые включают эти нестандартные представления для бесконечности и не-числа.

4. Детали функций

Следующие разделы обеспечивают дополнительные детали о работе различных функций JSON и операторов:

4.1. Функция json()

Функция json(X) проверяет, что ее аргумент X является действительной последовательностью JSON и возвращает уменьшенную версию этой последовательности JSON (со всеми ненужными удаленными пробелами). Если X не правильно построенная последовательность JSON, то это бросает ошибку.

Другими словами, эта функция преобразовывает необработанный текст, который похож на JSON в фактический JSON так, чтобы это могло быть передано в аргумент value некоторой другой функции json и интерпретировалось как JSON, а не последовательность. Эта функция не подходит для тестирования, является ли конкретная последовательность правильно построенным JSON, для этого используют json_valid() и/или json_error_position().

Если X в json(X) содержитобъекты JSON с двойными этикетками, то не определено, сохранены ли дубликаты. Текущее внедрение сохраняет дубликаты. Однако, будущие улучшения могут тихо удалить дубликаты./p>

Например:

• json(' { "this" : "is", "a": [ "test" ] } ') → '{"this":"is","a":["test"]}'

4.2. Функция json_array()

Функция json_array() принимает ноль или больше аргументов и возвращает правильно построенное множество JSON, которое составлено из тех аргументов. Если каким-либо аргументом json_array() является BLOB, ошибка брошена.

Аргумент с типом SQL TEXT обычно преобразовывается в указанную последовательность JSON. Однако, если аргумент это вывод от другой функции json1, то это сохранено как JSON. Это позволяет вызовам json_array() и json_object() быть вложенными. Функция json() может также использоваться, чтобы вынудить последовательности быть признанными JSON.

Примеры:

• json_array(1,2,'3',4) → '[1,2,"3",4]'
• json_array('[1,2]') → '["[1,2]"]'
• json_array(json_array(1,2)) → '[[1,2]]'
• json_array(1,null,'3','[4,5]','{"six":7.7}') → '[1,null,"3","[4,5]","{\"six\":7.7}"]'
• json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')) → '[1,null,"3",[4,5],{"six":7.7}]'

4.3. Функция json_array_length()

Функция json_array_length(X) возвращает число элементов в JSON-массиве X или 0, если X некоторое значение JSON кроме множества. json_array_length(X,P) определяет местонахождение множества в пути P в X и возвращает длину того множества, 0, если путь P определяет местонахождение элемента в X, который не является множеством JSON и NULL, если путь P не определяет местонахождение никакого элемента X. Ошибки брошены, если X не правильно построенный JSON или если P не правильно построенный путь.

Примеры:

• json_array_length('[1,2,3,4]') → 4
• json_array_length('[1,2,3,4]', '$') → 4
• json_array_length('[1,2,3,4]', '$[2]') → 0
• json_array_length('{"one":[1,2,3]}') → 0
• json_array_length('{"one":[1,2,3]}', '$.one') → 3
• json_array_length('{"one":[1,2,3]}', '$.two') → NULL

4.4. Функция json_error_position()

Функция json_error_positionf(X) возвращает 0, если вход X является правильно построенным JSON или последовательностью JSON5. Если вход X содержит одну или более синтаксических ошибок, то эта функция возвращает позицию символа первой синтаксической ошибки. Крайний левый символ 1.

Этот установленный порядок полезен по крайней мере для двух целей:

1. Чтобы определить является ли текстовая строка X действительным JSON или JSON5, как понято под SQLite, управляйте "json_error_position(X)==0". Это подобно json_valid() за исключением того, что json_valid(X) требует от X строго соответствовать каноническому JSON, тогда как json_error_position() позволяет расширения JSON5.

2. Используйте этот установленный порядок, чтобы найти местоположение синтаксической ошибки в большой последовательности JSON во время интерактивной отладки или чтобы произвести лучшие сообщения об ошибках для пользователей.

json_error_position() добавлена в SQLite version 3.42.0 (2023-05-16).

4.5. Функция json_extract()

Функция json_extract(X,P1,P2,...) извлекает и возвращает одно или более значений из JSON X. Если только единственный путь, который обеспечивается P1, то тип данных SQL результата NULL для пустого указателя JSON, INTEGER или REAL для числового значения JSON, INTEGER 0 для JSON false, INTEGER 1 для JSON true, текст для последовательности JSON, текстовое представление для объекта JSON и значений множества. Если есть аргументы разнообразного пути (P1, P2, и т.д.), тогда это возвращает текст SQLite, который является правильно построенным множеством JSON, содержащим различные значения.

Примеры:

• json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$') → '{"a":2,"c":[4,5,{"f":7}]}'
• json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c') → '[4,5,{"f":7}]'
• json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]') → '{"f":7}'
• json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f') → 7
• json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a') → '[[4,5],2]'
• json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]') → 5
• json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x') → NULL
• json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a') → '[null,2]'
• json_extract('{"a":"xyz"}', '$.a') → 'xyz'
• json_extract('{"a":null}', '$.a') → NULL

Есть тонкая несовместимость между json_extract() в SQLite и json_extract() в MySQL. MySQL-версия json_extract() всегда возвращает JSON. Версия SQLite json_extract() возвращает JSON только если есть два или больше параметра PATH (потому что результат тогда множество JSON) или если единственный параметр PATH ссылается на множество или объект. В SQLite, если у json_extract() есть только единственный параметр PATH и он ссылается на пустой указатель JSON или последовательность или числовое значение, тогда json_extract() возвращает соответствующий SQL NULL, TEXT, INTEGER или REAL.

Различие между MySQL json_extract() и SQLite json_extract() действительно выделяется только получая доступ к отдельным значениям в JSON, которые являются последовательностями или NULL. Следующая таблица демонстрирует различие:

4.6. Операторы -> и ->>

Начиная с SQLite version 3.38.0 (2022-02-22), операторы -> и ->> доступны для извлечения субкомпонентов JSON. Внедрение SQLite -> и ->> стремится быть совместимым с MySQL и с PostgreSQL. Операторы -> и ->> берут последовательность JSON в качестве своего левого операнда и выражения PATH или этикетки поля объекта или индекса массива как их правый операнд. Оператор -> возвращает представление JSON отобранного субкомпонента или NULL, если тот субкомпонент не существует. Оператор ->> вернет SQL TEXT, INTEGER, REAL или NULL, которое представляет отобранный субкомпонент или NULL, если субкомпонент не существует.

Операторы -> и ->> выбирают тот же самый субкомпонент JSON с левой стороны от них. Различие в том, что -> всегда возвращает представление JSON того субкомпонента, а ->> всегда возвращает представление SQL того субкомпонента. Таким образом эти операторы тонко отличаются от json_extract() с двумя аргументами. Вызов json_extract() с двумя аргументами возвратит представление JSON субкомпонента, если и только если субкомпонент это множество JSON или объект, и возвратит представление SQL субкомпонента, если субкомпонент это пустой указатель JSON, последовательность или числовое значение.

Правый операнд -> и ->> может быть правильно построенным выражением пути JSON. Это форма, используемая MySQL. Для совместимости с PostgreSQL операторы -> и ->> также принимают текстовую этикетку или целое число как их правый операнд. Если правильный операнд это текстовая этикетка X, то он интерпретируется как путь JSON '$.X'. Если правый операнд это целочисленное значение N, то он интерпретируется как путь JSON '$[N]'.

Примеры:

• '{"a":2,"c":[4,5,{"f":7}]}' -> '$' → '{"a":2,"c":[4,5,{"f":7}]}'
• '{"a":2,"c":[4,5,{"f":7}]}' -> '$.c' → '[4,5,{"f":7}]'
• '{"a":2,"c":[4,5,{"f":7}]}' -> 'c' → '[4,5,{"f":7}]'
• '{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2]' → '{"f":7}'
• '{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2].f' → '7'
• '{"a":2,"c":[4,5,{"f":7}]}' ->> '$.c[2].f' → 7
• '{"a":2,"c":[4,5,{"f":7}]}' -> 'c' -> 2 ->> 'f' → 7
• '{"a":2,"c":[4,5],"f":7}' -> '$.c[#-1]' → '5'
• '{"a":2,"c":[4,5,{"f":7}]}' -> '$.x' → NULL
• '[11,22,33,44]' -> 3 → '44'
• '[11,22,33,44]' ->> 3 → 44
• '{"a":"xyz"}' -> '$.a' → '"xyz"'
• '{"a":"xyz"}' ->> '$.a' → 'xyz'
• '{"a":null}' -> '$.a' → 'null'
• '{"a":null}' ->> '$.a' → NULL

4.7. Функции json_insert(), json_replace и json_set()

Функции json_insert(), json_replace и json_set() все берут единственное значение JSON в качестве их первого аргумента, сопровождаемого нолем или большим количеством пар пути, оценивают аргументы и возвращают новую последовательность JSON, сформированную, обновляя вход JSON парами path/value. Функции отличаются только по тому, как они имеют дело с созданием нового значения и переписыванием существующих ранее значений.

json_insert(), json_replace() и json_set() всегда берут нечетное число аргументов. Первый аргумент всегда это оригинальный JSON, который будет отредактирован. Последующие аргументы идут в парах с первым элементом каждой пары, являющейся путем и вторым элементом, являющимся значением, чтобы вставить, заменить или установить на том пути.

Редактирование происходит последовательно слева направо. Изменения, вызванные предшествующим редактированием, могут затронуть поиск пути последующего редактирования.

Если значение пары path/value это SQLite TEXT, оно обычно вставляется как указанная последовательность JSON, даже если последовательность похожа на действительный JSON. Однако, если значение это результат другой функции json (такой как json(), json_array() или json_object()) или если это результат оператора ->, тогда это интерпретируется как JSON и вставляется как JSON, сохраняющий весь его фундамент. Значения, которые являются результатом оператора ->>, всегда интерпретируются как TEXT и вставляются как последовательность JSON, даже если они похожи на действительный JSON.

Это бросает ошибку, если первый аргумент JSON не правильно построен, если какой-либо параметр PATH не правильно построен или если какой-либо аргумент BLOB.

Чтобы добавить элемент в конец множества, используйте json_insert() с индексом массива "#":

• json_insert('[1,2,3,4]','$[#]',99) → '[1,2,3,4,99]'
• json_insert('[1,[2,3],4]','$[1][#]',99) → '[1,[2,3,99],4]'

Еще примеры:

• json_insert('{"a":2,"c":4}', '$.a', 99) → '{"a":2,"c":4}'
• json_insert('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4,"e":99}'
• json_replace('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}'
• json_replace('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4}'
• json_set('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}'
• json_set('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4,"e":99}'
• json_set('{"a":2,"c":4}', '$.c', '[97,96]') → '{"a":2,"c":"[97,96]"}'
• json_set('{"a":2,"c":4}', '$.c', json('[97,96]')) → '{"a":2,"c":[97,96]}'
• json_set('{"a":2,"c":4}', '$.c', json_array(97,96)) → '{"a":2,"c":[97,96]}'

4.8. Функция json_object()

Функция json_object() принимает ноль или больше пар аргументов и возвращает правильно построенный объект JSON, который составлен из тех аргументов. Первый аргумент каждой пары это этикетка, второй аргумент каждой пары значение. Если каким-либо аргументом json_object() является BLOB, ошибка брошена.

Функция json_object() в настоящее время позволяет двойные этикетки, хотя это могло бы измениться в будущем улучшении.

Аргумент SQL TEXT обычно преобразовывается в указанную последовательность JSON, даже если входной текст правильно построенный JSON. Однако, если аргумент прямое следствие другой функции JSON или оператора -> (но не оператора ->>), то это рассматривают как JSON, вся его информация о типе JSON и фундамент сохранены. Это позволяет вызовам json_object() и json_array() быть вложенными. Функция json() может также использоваться, чтобы вынудить последовательности быть признанными JSON.

Примеры:

• json_object('a',2,'c',4) → '{"a":2,"c":4}'
• json_object('a',2,'c','{e:5}') → '{"a":2,"c":"{e:5}"}'
• json_object('a',2,'c',json_object('e',5)) → '{"a":2,"c":{"e":5}}'

4.9. Функция json_patch()

Функция json_patch(T,P) управляет RFC-7396 алгоритмом MergePatch, чтобы применить участок P против входа T. Исправленная копия T возвращена.

MergePatch может добавить, изменить или удалить элементы объекта JSON и таким образом для объектов JSON json_patch() это обобщенная замена для json_set() и json_remove(). Однако, MergePatch рассматривает объекты Array JSON как атомные. MergePatch не может добавить к Array или изменить в Array отдельные элементы. Это может только вставить, заменить или удалить целый Array как единый блок. Следовательно, json_patch() не так полезно, имея дело с JSON, который включает Array, особенно Array с большим количеством подструктур.

Примеры:

• json_patch('{"a":1,"b":2}','{"c":3,"d":4}') → '{"a":1,"b":2,"c":3,"d":4}'
• json_patch('{"a":[1,2],"b":2}','{"a":9}') → '{"a":9,"b":2}'
• json_patch('{"a":[1,2],"b":2}','{"a":null}') → '{"b":2}'
• json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}') → '{"a":9,"c":8}'
• json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}') → '{"a":{"x":1,"y":9},"b":3,"c":8}'

4.10. Функция json_remove()

Функция json_remove(X,P,...) берет единственное значение JSON в качестве своего первого аргумента, сопровождаемого нолем или большим количеством параметров path. json_remove(X,P,...) возвращает копию параметра X со всеми удаленными элементами, определенными параметрами пути. Пути, которые выбирают элементы, не найденные в X, тихо проигнорированы.

Удаления происходят последовательно слева направо. Изменения, вызванные предшествующими удалениями, могут затронуть поиск пути последующих аргументов.

Если json_remove(X) вызвана без параметров пути, то это возвращает вход X переформатированный с удалением избытка пробелов.

json_remove() бросает ошибку, если первый аргумент не правильно построенный JSON, если какой-либо более поздний аргумент не правильно построенный путь или если какой-либо аргумент BLOB.

Примеры:

• json_remove('[0,1,2,3,4]','$[2]') → '[0,1,3,4]'
• json_remove('[0,1,2,3,4]','$[2]','$[0]') → '[1,3,4]'
• json_remove('[0,1,2,3,4]','$[0]','$[2]') → '[1,2,4]'
• json_remove('[0,1,2,3,4]','$[#-1]','$[0]') → '[1,2,3]'
• json_remove('{"x":25,"y":42}') → '{"x":25,"y":42}'
• json_remove('{"x":25,"y":42}','$.z') → '{"x":25,"y":42}'
• json_remove('{"x":25,"y":42}','$.y') → '{"x":25}'
• json_remove('{"x":25,"y":42}','$') → NULL

4.11. Функция json_type()

Функция json_type(X) возвращает "type" наиболее удаленного элемента X. json_type(X,P) возвращает "type" элемента в X, который отобран путем P. "type" из json_type() является одним из следующих значений кода на SQL: 'null', 'true', 'false', 'integer', 'real', 'text', 'array' или 'object'. Если путь P в json_type(X,P) выбирает элемент, который не существует в X, то эта функция возвращает NULL.

json_type() бросает ошибку, если какой-либо из ее аргументов не правильно построен или является BLOB.

Примеры:

• json_type('{"a":[2,3.5,true,false,null,"x"]}') → 'object'
• json_type('{"a":[2,3.5,true,false,null,"x"]}','$') → 'object'
• json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a') → 'array'
• json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]') → 'integer'
• json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]') → 'real'
• json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]') → 'true'
• json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]') → 'false'
• json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]') → 'null'
• json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]') → 'text'
• json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]') → NULL

4.12. Функция json_valid()

Функция json_valid(X) вернет 1, если X является правильно построенный канонический RFC-7159 JSON без каких-либо расширений или 0, если аргумент X не является правильно построенным JSON или является JSON, который включает расширения JSON5.

Примеры:

• json_valid('{"x":35}') → 1
• json_valid('{"x":35') → 0
• json_valid(NULL) → NULL

Используйте выражение "json_error_position(X) ==0", чтобы определить, является ли последовательность правильно построенным JSON5. Используйте "json(X)", чтобы преобразовать JSON5 в канонический JSON.

4.13. Функция json_quote() function

Функция json_quote(X) преобразует значение SQL X (число или последовательность) в ее соответствующее представление JSON. Если X это значение JSON, возвращенное другой функцией JSON, то эта функция ничего не делает.

Примеры:

• json_quote(3.14159) → 3.14159
• json_quote('verdant') → '"verdant"'
• json_quote('[1]') → '"[1]"'
• json_quote(json('[1]')) → '[1]'
• json_quote('[1,') → '"[1,"'

4.14. Функции json_group_array() и json_group_object()

Функция json_group_array(X) это совокупная функция SQL, которая возвращает множество JSON, состоявшее из всех значений X в объединении. Точно так же json_group_object(NAME,VALUE) возвращает объект JSON, состоявший из всех пар NAME/VALUE в объединении.

4.15. Табличные функции json_each() и json_tree()

Табличные функции json_each(X) и json_tree(X) берут значение JSON, предоставленное как их первый аргумент и возвращают одну строку для каждого элемента. Функция json_each(X) обрабатывает только непосредственные потомки массива верхнего уровня, объекта или просто самого элемента верхнего уровня, если элемент верхнего уровня это примитивное значение. Функция json_tree(X) рекурсивно идет через фундамент JSON, начинающийся с элемента верхнего уровня.

Функции json_each(X,P) и json_tree(X,P) работают точно так же, как их коллеги с одним аргументом за исключением того, что они рассматривают элемент, определенный путем P как элемент верхнего уровня.

Схема таблицы, возвращенной json_each() и json_tree():

CREATE TABLE json_tree(
    key ANY,             -- key for current element relative to its parent
    value ANY,           -- value for the current element
    type TEXT,           -- 'object','array','string','integer', etc.
    atom ANY,            -- value for primitive types, null for array & object
    id INTEGER,          -- integer ID for this element
    parent INTEGER,      -- integer ID for the parent of this element
    fullkey TEXT,        -- full path describing the current element
    path TEXT,           -- path to the container of the current row
    json JSON HIDDEN,    -- 1st input parameter: the raw JSON
    root TEXT HIDDEN     -- 2nd input parameter: the PATH at which to start
);

"key" это индекс массива целого числа для элементов множества JSON и текстовой этикетки для элементов объекта JSON. Столбец key = NULL во всех других случаях.

Столбец "atom" это значение SQL, соответствующее примитивным элементам, то есть элементам кроме множеств JSON и объектов. Колонка "atom" = NULL для множества или объекта JSON. Колонка "value" совпадает с колонкой "atom" для примитивных элементов JSON, но берет текст значения JSON для множеств и объектов.

Столбец "type" это текстовое значение SQL ('null', 'true', 'false', 'integer', 'real', 'text', 'array', 'object'), согласно типу текущего элемента JSON.

Столбец "id" это integer, который определяет элемент JSON в полной последовательности JSON. "id" integer это внутреннее вспомогательное число, вычисление которого могло бы измениться в будущих выпусках. Единственная гарантия это что столбец "id" будет отличаться для каждой строки.

Столбец "parent" всегда NULL для json_each(). Для json_tree() столбец "parent" это "id" integer для родителя текущего элемента или NULL для элемента JSON верхнего уровня или элемента, определенного корневым путем во втором аргументе.

Столбец "fullkey" это текстовый путь, который однозначно определяет текущий элемент строки в оригинальной последовательности JSON. Полный ключ к истинному элементу верхнего уровня возвращен, даже если альтернативная отправная точка обеспечивается аргументом "root".

Столбец "path" это путь ко множеству или контейнеру объекта, который содержит текущую строку или путь к текущей строке в случае, где итерации начинаются на примитивном типе и таким образом только обеспечивают единственную строку вывода.

4.15.1. Примеры использования json_each() и json_tree()

Допустим таблица "CREATE TABLE user(name,phone)" хранит ноль или больше номеров телефона как объект множества JSON в поле user.phone. Чтобы найти всех пользователей, у которых есть любой номер телефона с кодом области 704:

SELECT DISTINCT user.name FROM user, json_each(user.phone)
       WHERE json_each.value LIKE '704-%';

Теперь предположите, что поле user.phone содержит простой текст, если у пользователя есть только единственный номер телефона и множество JSON, если у пользователя есть многократные номера телефона. Тот же самый запрос. Но теперь функция json_each() может быть вызвана только для тех пользователей, у которых есть два или больше номера телефона, так как json_each() требует правильно построенного JSON как первый аргумент:

SELECT name FROM user WHERE phone LIKE '704-%'
       UNION SELECT user.name FROM user, json_each(user.phone)
                    WHERE json_valid(user.phone) AND
                    json_each.value LIKE '704-%';

Создайте иную базу данных с "CREATE TABLE big(json JSON)". Чтобы получить полную построчную декомпозицию данных:

SELECT big.rowid, fullkey, value FROM big, json_tree(big.json)
       WHERE json_tree.type NOT IN ('object','array');

В предыдущем термин "type NOT IN ('object','array')" в WHERE подавляет контейнеры и пропускает только элементы листа. Тот же самый эффект мог быть достигнут так:

SELECT big.rowid, fullkey, atom FROM big, json_tree(big.json)
       WHERE atom IS NOT NULL;

Предположим, что каждый вход в таблице BIG это объект JSON с полем '$.id', который является уникальным идентификатором, и полем '$.partlist', который может быть очень вложенным объектом. Вы хотите найти id каждого входа, который содержит одну или более ссылок на uuid '6fa5181e-5721-11e5-a04e-57f3d7b32808' где угодно в его '$.partlist'.

SELECT DISTINCT json_extract(big.json,'$.id')
       FROM big, json_tree(big.json, '$.partlist')
       WHERE json_tree.key='uuid' AND
       json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';