Функции JSON в SQLite: полный справочник и JSONB

Материал основан на разборе sqlite.org. Ниже — главное и практические шаги, которые можно быстро применить в работе.

---

Функции JSON в SQLite: обзор

SQLite предоставляет тридцать функций и два оператора для работы со значениями JSON, а также четыре табличные функции для разбора JSON строк. Эти функции отмечены флагами SQLITE_INNOCUOUS и SQLITE_DETERMINISTIC.

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

json ( json )

jsonb ( json )

json -> path

Существуют четыре агрегатные SQL-функции:

Четыре табличные функции:

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

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

Интерфейс JSON-функций SQLite

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

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

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

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

JSONB: двоичное представление JSON в SQLite

Начиная с версии 3.45.0 (2024-01-15), SQLite позволяет хранить внутреннее представление JSON в формате BLOB, называемом «JSONB». Это существенно сокращает накладные расходы на парсинг и обработку JSON при работе с данными, так как формат JSONB занимает меньше места на диске.

Параметры SQL-функций, принимающие текст на основе JSON, также принимают BLOB в формате JSONB, что делает выполнение операций быстрее, так как не требуется запуск парсера JSON.

Большинство SQL-функций, возвращающие текстовый JSON, имеют соответствующие функции для возврата JSONB. Функции, возвращающие JSON, начинаются с «json_», тогда как функции для JSONB — с «jsonb_».

3.2.1. Формат JSONB

JSONB — это внутреннее двоичное представление JSON в SQLite. Приложения не должны использовать JSONB за пределами SQLite.

Формат хранения JSONB в SQLite отличается от PostgreSQL и не является двоично совместимым. В PostgreSQL JSONB оптимизирован для быстрого поиска, в SQLite большинство операций имеют временна́я сложность O(N) с акцентом на компактность.

3.2.2. Обработка некорректного JSONB

SQLite гарантирует корректность генерируемого JSONB, но возможны случаи некорректных BLOB, которые могут вызвать неожиданные результаты.

SQL-оператор может завершиться с ошибкой «malformed JSON» (некорректный JSON).

Может быть возвращён правильный ответ, если некорректные части JSONB-блоба не влияют на результат.

Может быть возвращён нелепый или бессмысленный ответ.

Методы обработки недопустимого JSONB могут изменяться с версиями SQLite. Рекомендуется использовать функцию json_valid() для проверки корректности JSONB.

Разработчики дают одно обещание: некорректный JSONB никогда не вызовет ошибку памяти или аналогичную проблему, которая могла бы привести к уязвимости. Недопустимый JSONB может привести к странным ответам или вызвать прерывание запросов, но не приведёт к аварийному завершению.

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

Для функций, принимающих аргументы PATH (путь), этот PATH должен быть корректно сформирован, иначе функция выбросит ошибку. Корректно сформированный PATH — это текстовое значение, начинающееся ровно с одного символа «$», за которым следует ноль или более вхождений «.objectlabel» или «[arrayindex]».

Значение arrayindex (индекс массива) обычно является неотрицательным целым числом N. В этом случае выбирается N-й элемент массива, начиная с нуля слева. Значение arrayindex также может иметь вид «#-N», в котором случае выбирается N-й элемент с правого конца. Последний элемент массива — это «#-1». Символ «#» следует воспринимать как «количество элементов в массиве».

Тогда выражение «#-1» вычисляется как целое число, соответствующее последней записи в массиве. Иногда полезно использовать в качестве индекса массива просто символ #, например при добавлении значения в конец существующего массива JSON:

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

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

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

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

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

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

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

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

Текущая реализация этой библиотеки JSON использует парсер с рекурсивным спуском (recursive descent parser). Во избежание избыточного использования стека любой JSON-ввод с более чем 1000 уровнями вложенности считается недопустимым. Ограничения на глубину вложенности допускаются для совместимых реализаций JSON согласно разделу 9 RFC-8259.

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

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

Ниже приведён краткий обзор расширений JSON5 (адаптировано из спецификации JSON5):

Ключи объектов могут быть идентификаторами без кавычек.

Объекты могут иметь одну завершающую запятую.

Массивы могут иметь одну завершающую запятую.

Строки могут быть заключены в одинарные кавычки.

Строки могут занимать несколько строк с помощью экранирования символов новой строки.

Строки могут включать новые символы экранирования.

Числа могут быть шестнадцатеричными.

Числа могут иметь ведущую или завершающую десятичную точку.

Числа могут быть «Infinity», «-Infinity» и «NaN».

Числа могут начинаться с явного знака плюс.

Допускаются однострочные (//…) и многострочные (/…/) комментарии.

Допускаются дополнительные символы пробела.

Чтобы преобразовать строку X из JSON5 в канонический JSON, вызовите «json(X)». Вывод функции «json()» будет каноническим JSON независимо от любых расширений JSON5, присутствующих во входных данных.

Для обратной совместимости функция json_valid(X) без аргумента «flags» по-прежнему возвращает false для входных данных, не являющихся каноническим JSON, даже если входные данные представляют собой JSON5, который функция способна понять. Чтобы определить, является ли входная строка допустимым JSON5, включите бит 0x02 в аргумент «flags» функции json_valid: «json_valid(X,2)».

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

Строгий JSON5 требует, чтобы ключи объектов без кавычек были ECMAScript 5.1 IdentifierNames. Однако для определения того, является ли ключ ECMAScript 5.1 IdentifierName, требуются большие таблицы Unicode и значительный объём кода. По этой причине SQLite допускает, чтобы ключи объектов включали любые символы Unicode, превышающие U+007f, которые не являются пробельными символами.

Это расширенное определение «идентификатора» значительно упрощает реализацию и позволяет парсеру JSON быть меньше и работать быстрее.

JSON5 допускает выражение бесконечностей с плавающей точкой как «Infinity», «-Infinity» или «+Infinity» именно в таком регистре — начальная «I» заглавная, все остальные символы строчные. SQLite также допускает использование сокращения «Inf» вместо «Infinity» и позволяет обоим ключевым словам появляться в любой комбинации прописных и строчных букв. Аналогично, JSON5 допускает «NaN» для нечисловых значений.

SQLite расширяет это, также допуская «QNaN» и «SNaN» в любой комбинации прописных и строчных букв. Обратите внимание, что SQLite интерпретирует NaN, QNaN и SNaN просто как альтернативные написания «null». Это расширение было добавлено потому, что в реальных данных существует много JSON, включающего эти нестандартные представления бесконечности и нечисловых значений.

Производительность JSON-функций в SQLite

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

По этой причине, когда аргумент одной JSON-функции предоставляется другой JSON-функцией, обычно эффективнее использовать вариант «jsonb_» для функции, используемой в качестве аргумента. На практике я рекомендую придерживаться этого правила везде, где это возможно:

… json_insert(A,'$.b',json(C)) … ← Менее эффективно.

… json_insert(A,'$.b',jsonb(C)) … ← Более эффективно.

Агрегатные JSON SQL-функции являются исключением из этого правила. Все эти функции выполняют обработку с использованием текста, а не JSONB. Поэтому для агрегатных JSON SQL-функций эффективнее, чтобы аргументы предоставлялись с использованием функций «json_», а не «jsonb_»:

… json_group_array(json(A))) … ← Более эффективно.

… json_group_array(jsonb(A))) … ← Менее эффективно.

3.8. Ошибка с BLOB-входными данными в JSON

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

Но в реальной реализации входные данные принимались, если содержимое BLOB представляло собой допустимую строку JSON в текстовой кодировке базы данных.

Эта ошибка с BLOB-входными данными в JSON была случайно исправлена при переработке JSON-процедур в версии 3.45.0 (2024-01-15). Это привело к поломкам в приложениях, которые стали зависеть от старого поведения.

В защиту этих приложений: их нередко подталкивало к использованию BLOB в качестве JSON то, что функция SQL readfile() в CLI возвращает BLOB. readfile() использовалась для чтения JSON из файлов на диске, но возвращала BLOB. И это работало, так почему бы и нет?

В целях обратной совместимости с ошибочным поведением прежнее (ранее некорректное) поведение интерпретации BLOB как текстового JSON, если никакая другая интерпретация не работает, задокументировано и официально поддерживается в версии 3.45.1 (2024-01-30) и всех последующих выпусках.

Однако следует учитывать, что существуют BLOB, которые одновременно являются допустимым JSONB и допустимым JSON после приведения к тексту. Например, BLOB x'33343535' является допустимым JSONB, а именно целым числом 456, а при приведении к тексту — допустимым целочисленным литералом JSON 3456.

Поэтому, если у вас есть устаревшие базы данных, в которых JSON-текст хранится в виде BLOB, и вы хотите продолжать использовать эти базы данных с SQLite 3.45.0 или более поздней версией, рекомендуется выполнить UPDATE для этого устаревшего JSON-текста, чтобы он хранился именно как TEXT, а не как BLOB.

4. Подробное описание функций

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

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

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

Если входные данные представляют собой текст JSON5, то перед возвратом они преобразуются в канонический текст RFC-8259.

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

Пример:

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

Функция jsonb(X) возвращает двоичное JSONB-представление JSON, переданного в качестве аргумента X. Если X является TEXT без допустимого синтаксиса JSON, то выбрасывается ошибка.

Если X является BLOB и выглядит как JSONB, то эта процедура просто возвращает копию X. Однако проверяется только самый внешний элемент JSONB-входных данных. Глубокая структура JSONB не валидируется.

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

SQL-функция 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]"]'

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

SQL-функция jsonb_array() работает так же, как функция json_array(), за исключением того, что возвращает построенный JSON-массив в приватном формате JSONB SQLite, а не в стандартном текстовом формате RFC 8259.

4.5. Функции json_array_insert() и jsonb_array_insert()

SQL-функции json_array_insert() и jsonb_array_insert() работают аналогично функциям json_replace() и jsonb_replace(), с двумя отличиями:

Аргументы PATH должны ссылаться на элемент массива. Если какой-либо аргумент PATH не заканчивается ссылкой на элемент массива (если PATH не заканчивается на «[…]»), то выбрасывается ошибка.

Вместо перезаписи существующих элементов массива массив расширяется, а новые элементы вставляются в позицию, указанную аргументом PATH.

Как и в случае с json_replace() и jsonb_replace(), аргументы PATH и VALUE могут повторяться несколько раз. Возвращается изменённый JSON: в текстовом формате для json_array_insert() или в двоичном BLOB-формате для jsonb_array_insert().

4.6. Функция 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 не является корректно сформированным путём.

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

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

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

Функция json_error_position() была добавлена в SQLite версии 3.42.0 (2023-05-16).

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

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

Если указано несколько аргументов-путей (P1, P2 и так далее), то функция возвращает текст SQLite, представляющий собой корректный JSON-массив, содержащий различные значения.

Между функцией json_extract() в SQLite и функцией json_extract() в MySQL существует тонкое несовместимое различие. Версия MySQL json_extract() всегда возвращает JSON. Версия SQLite json_extract() возвращает JSON только в том случае, если указано два или более аргументов PATH (поскольку результатом тогда является JSON-массив) или если единственный аргумент PATH ссылается на массив или объект.

В SQLite, если json_extract() имеет только один аргумент PATH и этот PATH ссылается на JSON null, строку или числовое значение, то json_extract() возвращает соответствующее значение SQL NULL, TEXT, INTEGER или REAL.

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

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

Функция jsonb_extract() работает так же, как функция json_extract(), за исключением случаев, когда json_extract() в норме возвращала бы текстовый JSON-массив или объект — в этих случаях данная функция возвращает массив или объект в формате JSONB. В обычных случаях, когда возвращается текстовый, числовой, null или булев элемент JSON, эта функция работает точно так же, как json_extract().

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

Начиная с версии SQLite 3.38.0 (2022-02-22), для извлечения подкомпонентов JSON доступны операторы -> и ->>. Реализация -> и ->> в SQLite стремится быть совместимой как с MySQL, так и с PostgreSQL. Операторы -> и ->> принимают строку JSON или JSONB-блоб в качестве левого операнда, а выражение PATH, метку поля объекта или индекс массива — в качестве правого операнда.

Оператор -> возвращает текстовое JSON-представление выбранного подкомпонента или NULL, если этот подкомпонент не существует. Оператор ->> возвращает значение SQL TEXT, INTEGER, REAL или NULL, представляющее выбранный подкомпонент, или NULL, если подкомпонент не существует.

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

Вызов json_extract() с двумя аргументами вернёт JSON-представление подкомпонента тогда и только тогда, когда подкомпонент является JSON-массивом или объектом, и вернёт SQL-представление подкомпонента, если подкомпонент является JSON null, строкой или числовым значением.

Когда оператор -> возвращает JSON, он всегда возвращает текстовое представление этого JSON согласно RFC 8565, а не JSONB. Используйте функцию jsonb_extract(), если вам нужен подкомпонент в формате JSONB.

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

Если правый операнд — целое число N, то при неотрицательном значении оно интерпретируется как JSON-путь '$[N]'. Если же N — отрицательное целое число со значением -K, то оно интерпретируется как JSON-путь '$[#-K]'. Иными словами, индексирование начинается с конца массива и движется к его началу. Отрицательные значения N поддерживаются только в версиях SQLite 3.47.0 (2024-10-21) и более поздних.

'{"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.11. Функции json_insert(), json_replace() и json_set()

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

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

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

Если значение в паре «путь — значение» является текстовым значением 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_array_insert() для вставки элементов в начало или середину массива.

Другие примеры:

4.12. Функции jsonb_insert(), jsonb_replace() и jsonb_set()

Функции jsonb_insert(), jsonb_replace() и jsonb_set() работают так же, как json_insert(), json_replace() и json_set() соответственно, за исключением того, что версии с префиксом «jsonb_» возвращают результат в бинарном формате JSONB.

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

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

В настоящее время функция json_object() допускает дублирующиеся метки без предупреждения, хотя это может измениться в будущих версиях.

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

Функцию json() также можно использовать для принудительного распознавания строк как JSON.

4.14. Функция jsonb_object()

Функция jsonb_object() работает так же, как функция json_object(), за исключением того, что сгенерированный объект возвращается в бинарном формате JSONB.

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

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

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

Поэтому json_patch() менее полезна при работе с JSON, содержащим массивы, особенно массивы с разветвлённой подструктурой.

4.16. Функция jsonb_patch()

Функция jsonb_patch() работает так же, как функция json_patch(), за исключением того, что изменённый JSON возвращается в бинарном формате JSONB.

4.17. Функция json_pretty()

Функция json_pretty() работает как json(), но добавляет дополнительные пробельные символы, чтобы результат JSON было удобнее читать человеку. Первый аргумент — это JSON или JSONB, который нужно отформатировать для удобного чтения. Необязательный второй аргумент — текстовая строка, используемая для отступов. Если второй аргумент опущен или равен NULL, отступ составляет четыре пробела на каждый уровень.

Функция json_pretty() была добавлена в SQLite версии 3.46.0 (2024-05-23).

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

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

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

Если функция json_remove(X) вызывается без аргументов-путей, она возвращает входное значение X в переформатированном виде, с удалёнными лишними пробельными символами.

Функция json_remove() выбрасывает ошибку, если первый аргумент не является корректным JSON или если какой-либо последующий аргумент не является корректным путём.

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]'

4.19. Функция jsonb_remove()

Функция jsonb_remove() работает точно так же, как функция json_remove(), за исключением того, что изменённый результат JSON возвращается в бинарном формате JSONB.

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

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

Функция json_type() выбрасывает ошибку, если её первый аргумент не является корректным JSON или JSONB, либо если второй аргумент не является корректным путём JSON.

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

Функция json_valid(X,Y) возвращает 1, если аргумент X является корректным JSON, или 0, если X не является корректным. Параметр Y — целочисленная битовая маска, определяющая, что подразумевается под «корректным». В настоящее время определены следующие биты Y:

0x01 → Входные данные представляют собой текст, строго соответствующий каноническому JSON по RFC-8259, без каких-либо расширений.

0x02 → Входные данные представляют собой текст JSON с расширениями JSON5, описанными выше.

0x04 → Входные данные представляют собой BLOB, который внешне выглядит как JSONB.

0x08 → Входные данные представляют собой BLOB, строго соответствующий внутреннему формату JSONB.

Комбинируя биты, можно получить следующие полезные значения Y:

1 → X является текстом JSON по RFC-8259

2 → X является текстом JSON5

4 → X, вероятно, является JSONB

5 → X является текстом JSON по RFC-8259 или JSONB

6 → X является текстом JSON5 или JSONB ← Вероятно, это то значение, которое вам нужно

8 → X является строго соответствующим JSONB

9 → X соответствует RFC-8259 или строго соответствующему JSONB

10 → X является JSON5 или строго соответствующим JSONB

Параметр Y является необязательным. Если он опущен, по умолчанию принимается значение 1, что означает: поведение по умолчанию — возвращать true только в том случае, если входные данные X строго соответствуют тексту JSON по RFC-8259 без каких-либо расширений. Это обеспечивает совместимость однoаргументной версии json_valid() со старыми версиями SQLite, предшествующими добавлению поддержки JSON5 и JSONB.

Разница между битами 0x04 и 0x08 в параметре Y состоит в том, что 0x04 проверяет только внешнюю оболочку BLOB, чтобы убедиться, что он внешне похож на JSONB. Этого достаточно для большинства целей и выполняется очень быстро. Бит 0x08 выполняет тщательную проверку всех внутренних деталей BLOB. Время работы бита 0x08 линейно зависит от размера входных данных X и значительно медленнее. Для большинства целей рекомендуется использовать бит 0x04.

Если вы просто хотите узнать, является ли значение допустимыми входными данными для одной из других функций JSON, вероятно, вам следует использовать значение Y равное 6.

Любое значение Y меньше 1 или больше 15 вызывает ошибку в последней версии json_valid(). Однако будущие версии json_valid() могут быть расширены для принятия значений флагов за пределами этого диапазона с новыми значениями, которые ещё не были предусмотрены.

Если любой из входных параметров X или Y функции json_valid() равен NULL, функция возвращает NULL.

json_valid('{"x":35}') → 1 json_valid('{x:35}') → 0 json_valid('{x:35}',6) → 1 json_valid('{"x":35') → 0

4.22. Функция json_quote()

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

json_quote(3.14159) → 3.14159 json_quote('[1]') → '"[1]"' json_quote('[1,') → '"[1,"'

4.23. Агрегатные функции для массивов и объектов

Функция json_group_array(X) является агрегатной функцией SQL, которая возвращает массив JSON, состоящий из всех значений X в агрегации. Аналогично, функция json_group_object(NAME,VALUE) возвращает объект JSON, состоящий из всех пар NAME/VALUE в агрегации. Варианты с префиксом «jsonb_» работают так же, за исключением того, что возвращают результат в бинарном формате JSONB.

4.24. Табличные функции для разбора

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

Функции json_tree(X) и jsonb_tree(X) рекурсивно обходят всю подструктуру JSON, начиная с элемента верхнего уровня.

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

Варианты jsonb_each() и jsonb_tree() доступны начиная с версии SQLite 3.51.0 (2025-11-04). Эти два варианта работают так же, как их аналоги без буквы «b», за исключением того, что столбец «value» возвращает JSONB вместо текстового JSON, когда столбец «type» имеет значение 'object' или 'array'.

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

CREATE TABLE json_tree( key ANY, -- ключ текущего элемента относительно его родителя value ANY, -- значение текущего элемента type TEXT, -- 'object','array','string','integer' и т.д. atom ANY, -- значение для примитивных типов, null для массива и объекта id INTEGER, -- целочисленный идентификатор данного элемента parent INTEGER, -- целочисленный идентификатор родителя данного элемента fullkey TEXT, -- полный путь, описывающий текущий элемент path TEXT, -- путь к контейнеру текущей строки json JSON HIDDEN, -- 1-й входной параметр: исходный JSON root TEXT HIDDEN -- 2-й входной параметр: PATH, с которого начинается обход
);

Столбец «key» содержит целочисленный индекс массива для элементов JSON-массива и текстовую метку для элементов JSON-объекта. В остальных случаях столбец key имеет значение NULL.

Столбец «value» содержит SQL-значение для элемента JSON, обозначенного через «key» или «fullkey». Если «type» элемента является одним из 'null', 'true', 'false', 'integer', 'real' или 'text', то соответствующее SQL-значение этого элемента возвращается в качестве «value».

Когда «type» равен 'array' или 'object', столбец «value» вернёт текстовый JSON массива или объекта в случае json_each() и json_tree(), либо JSONB массива или объекта в случае jsonb_each() или jsonb_tree(). Формат столбца «value» является единственным поведенческим различием между json_each() и jsonb_each(), а также между json_tree() и jsonb_tree().

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

Столбец «atom» содержит SQL-значение, соответствующее примитивным элементам — элементам, не являющимся JSON-массивами и объектами. Столбец «atom» имеет значение NULL для JSON-массива или объекта.

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

Столбец «parent» всегда имеет значение NULL для json_each(). Для json_tree() столбец «parent» содержит целое число «id» родителя текущего элемента, либо NULL для элемента верхнего уровня JSON или элемента, идентифицированного корневым путём во втором аргументе.

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

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

4.24.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-массив, если у пользователя несколько номеров. Задаётся тот же вопрос: «Какие пользователи имеют номер телефона с кодом города 704?» Но теперь функцию 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', которое может быть глубоко вложенным объектом. Требуется найти идентификатор каждой записи, содержащей одну или несколько ссылок на 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';

5. Типичные ошибки при работе с JSON-функциями SQLite

Работая с JSON-функциями SQLite, я замечаю несколько ошибок, которые встречаются особенно часто.

Передача BLOB вместо TEXT. До версии 3.45.0 SQLite молча принимал BLOB с текстовым содержимым JSON. Начиная с 3.45.0 поведение изменилось, и приложения, использовавшие readfile() для загрузки JSON из файлов, могли сломаться. Если вы храните JSON в виде BLOB в устаревших базах данных, выполните UPDATE и приведите значения к типу TEXT.

Путаница между операторами -> и ->>. Оператор -> всегда возвращает JSON-представление подкомпонента, тогда как ->> возвращает SQL-значение. Это означает, что строка, извлечённая через ->, будет содержать кавычки, а через ->> — нет. Передача результата ->> в качестве аргумента value другой JSON-функции приведёт к тому, что значение будет интерпретировано как TEXT, а не как JSON.

Неправильный выбор между json_ и jsonb_ в агрегатных функциях. Агрегатные функции json_group_array() и json_group_object() работают с текстом, а не с JSONB. Поэтому передавать в них аргументы через jsonb_-функции менее эффективно, чем через json_-функции.

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

Использование json_valid() без флага для JSON5. По умолчанию json_valid(X) возвращает 0 для корректного JSON5, если тот не является одновременно каноническим JSON. Чтобы проверить JSON5, используйте json_valid(X,2) или json_valid(X,6).

Ответы на эти вопросы могут быть для вас полезными

Чем отличается JSONB в SQLite от JSONB в PostgreSQL?

Это два разных формата с одинаковым названием. Они не являются двоично совместимыми. JSONB в PostgreSQL обеспечивает поиск за O(1), тогда как JSONB в SQLite имеет временна́я сложность O(N) для большинства операций. Преимущество JSONB в SQLite — компактность и скорость по сравнению с текстовым JSON, потенциально в несколько раз.

Когда лучше использовать jsonb_-функции вместо json_-функций?

Используйте jsonb_-варианты, когда результат одной функции передаётся в качестве аргумента другой: это позволяет избежать лишнего цикла разбора текста в JSONB. Исключение — агрегатные функции json_group_array() и json_group_object(), которые работают с текстом, поэтому для них эффективнее передавать аргументы через json_-функции.

Как проверить, является ли строка допустимым JSON5, а не только каноническим JSON?

Используйте json_valid(X,2) для проверки JSON5 или json_valid(X,6) для проверки JSON5 и JSONB одновременно. Вызов json_valid(X) без второго аргумента по умолчанию проверяет только строгое соответствие RFC-8259.

Как добавить элемент в конец JSON-массива?

Используйте json_insert() с индексом «#»: json_insert('[1,2,3]','$[#]',99) вернёт '[1,2,3,99]'. Символ «#» интерпретируется как количество элементов в массиве, то есть указывает на позицию сразу после последнего элемента.

Почему json_extract() в SQLite и MySQL возвращают разные результаты для строк и null?

Версия MySQL всегда возвращает JSON. Версия SQLite возвращает SQL-значение (TEXT, INTEGER, REAL или NULL), если указан один PATH и он ссылается на примитивное значение. Это различие особенно заметно при извлечении строк: MySQL вернёт строку в кавычках как JSON, SQLite — строку без кавычек как TEXT.