Функции и операторы JSON

1. Обзор

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

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

1. json(json)
2. jsonb(json)
3. json_array(value1,value2,...)
4. jsonb_array(value1,value2,...)
5. jsonb_array_insert(json,path,value,...)
6. json_array_insert(json,path,value,...)
7. json_array_length(json)
   json_array_length(json,path)
8. json_error_position(json)
9. json_extract(json,path,...)
10. jsonb_extract(json,path,...)
11. json -> path
12. json ->> path
13. json_insert(json,path,value,...)
14. jsonb_insert(json,path,value,...)
15. json_object(label1,value1,...)
16. jsonb_object(label1,value1,...)
17. json_patch(json1,json2)
18. jsonb_patch(json1,json2)
19. json_pretty(json)
20. json_remove(json,path,...)
21. jsonb_remove(json,path,...)
22. json_replace(json,path,value,...)
23. jsonb_replace(json,path,value,...)
24. json_set(json,path,value,...)
25. jsonb_set(json,path,value,...)
26. json_type(json)
    json_type(json,path)
27. json_valid(json)
    json_valid(json,flags)
28. json_quote(value)

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

29. json_group_array(value)
30. jsonb_group_array(value)
31. json_group_object(label,value)
32. jsonb_group_object(name,value)

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

33. json_each(json)
    json_each(json,path)
34. json_tree(json)
    json_tree(json,path)
35. jsonb_each(json)
    jsonb_each(json,path)
36. jsonb_tree(json)
    jsonb_tree(json,path)

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

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

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

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

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

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

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

3.2. JSONB

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

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

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

3.2.1. Формат JSONB

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

Название «JSONB» вдохновлено PostgreSQL, однако формат JSONB SQLite на диске отличается от формата PostgreSQL. У этих двух форматов одинаковое название, но они несовместимы на двоичном уровне. Формат JSONB PostgreSQL, как утверждается, обеспечивает поиск элементов в объектах и массивах за O(1). Формат JSONB SQLite такого не обещает. Для большинства операций в SQLite временная сложность JSONB SQLite составляет O(N), как и для текстового JSON. Преимущество JSONB в SQLite состоит в том, что он занимает меньше места и работает быстрее текстового JSON — потенциально в несколько раз быстрее. Формат JSONB на диске предусматривает возможность добавления улучшений, и в будущих версиях SQLite могут появиться параметры, обеспечивающие поиск элементов JSONB за O(1), однако сейчас такой возможности нет.

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

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

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

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

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

Способ обработки некорректного JSONB в SQLite может меняться от версии к версии. Система следует принципу «мусор на входе — мусор на выходе»: если передать функциям JSON некорректный JSONB, результат тоже будет некорректным. Если вы сомневаетесь в корректности JSONB, проверьте его с помощью функции json_valid().

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

3.3. Аргументы 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.4. Аргументы VALUE

В функциях, принимающих аргументы «value» (также обозначаемые как «value1» и «value2»), эти аргументы обычно рассматриваются как литеральные строки, которые заключаются в кавычки и становятся строковыми значениями JSON в результате. Даже если входные строки value выглядят как правильно сформированный 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. Такие последовательности не преобразуются и не обрабатываются специальным образом; функции JSON SQLite рассматривают их как обычный текст.

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

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

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

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

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

• Ключи объектов могут быть не заключёнными в кавычки идентификаторами.
• После последнего элемента объекта может стоять одна завершающая запятая.
• После последнего элемента массива может стоять одна завершающая запятая.
• Строки могут заключаться в одинарные кавычки.
• Строки могут занимать несколько строк, если символы новой строки экранированы.
• В строках могут использоваться новые escape-последовательности.
• Числа могут быть шестнадцатеричными.
• Перед числом или после него может стоять десятичная точка.
• Числа могут быть «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 двумя способами:

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

2. JSON5 допускает запись бесконечностей с плавающей точкой в виде «Infinity», «-Infinity» или «+Infinity» только в указанном регистре: первая буква «I» заглавная, а все остальные буквы строчные. SQLite также допускает сокращение «Inf» вместо «Infinity» и позволяет записывать оба ключевых слова в любом сочетании заглавных и строчных букв. Аналогично, JSON5 допускает «NaN» для обозначения значения «не число». SQLite расширяет это правило, также допуская «QNaN» и «SNaN» в любом сочетании заглавных и строчных букв. Обратите внимание, что SQLite интерпретирует NaN, QNaN и SNaN просто как альтернативные написания значения «null». Это расширение было добавлено потому, что, как нам сообщили, в реальных данных широко распространён JSON с такими нестандартными обозначениями бесконечности и значения «не число».

3.7. Вопросы производительности

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

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

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

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

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

3.8. Ошибка обработки входных данных JSON BLOB

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

Эта ошибка обработки входных данных JSON BLOB была случайно исправлена при повторной реализации процедур JSON для выпуска 3.45.0 (2024-01-15). Это нарушило работу приложений, которые полагались на прежнее поведение. (В защиту этих приложений: часто их подталкивала к использованию BLOB-объектов в качестве JSON SQL-функция readfile(), доступная в CLI. Readfile() использовалась для чтения JSON с диска, но readfile() возвращает 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 или BLOB-объектом JSONB, и возвращает минимизированную версию этой строки JSON, из которой удалены все ненужные пробелы. Если X не является правильно сформированной строкой JSON или BLOB-объектом JSONB, эта процедура выдаёт ошибку.

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

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

Пример:

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

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

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

Если X является BLOB-объектом и похож на JSONB, эта процедура просто возвращает его копию. Однако проверяется только самый внешний элемент входного 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]"]'
• 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.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, но с двумя отличиями:

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

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

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

Примеры:

• json_array_insert('[1,2,3]','$[1]','new') → [1,"new",2,3]
• json_array_insert('{"a":[1,2,3]}', '$.a[0]','new') → {"a":["new",1,2,3]}

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

Примеры:

• 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.7. Функция json_error_position()

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

Если входное значение X является BLOB-объектом, процедура возвращает 0, если X является правильно сформированным BLOB-объектом 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('{"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. Версия json_extract() для MySQL всегда возвращает JSON. Версия json_extract() для SQLite возвращает JSON только при наличии двух или более аргументов PATH (поскольку результат в таком случае является массивом JSON) или если единственный аргумент PATH указывает на массив или объект. В SQLite, если у json_extract() есть только один аргумент PATH и этот PATH указывает на значение JSON null, строку или число, json_extract() возвращает соответствующее значение SQL NULL, TEXT, INTEGER или REAL.

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

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, используйте функцию jsonb_extract().

Правый операнд операторов -> и ->> может быть корректно сформированным выражением пути 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().

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

• 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.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.

Примеры:

• 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.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, содержащим массивы, особенно массивы со сложной внутренней структурой.

Примеры:

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

Примеры:

• 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.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 — это текст JSON RFC-8259 или строго соответствующий формату JSONB объект
• 10 → X — это текст JSON5 или строго соответствующий формату JSONB объект

Параметр Y является необязательным. Если он опущен, по умолчанию используется значение 1. Это означает, что по умолчанию функция возвращает true только в том случае, если входное значение X — это текст JSON, строго соответствующий RFC-8259 и не содержащий расширений. Благодаря этому версия 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
• json_valid(NULL) → NULL

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

Функция 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.23. Агрегатные функции для массивов и объектов

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

4.24. Табличные функции для разбора JSON: json_each(), jsonb_each(), json_tree() и jsonb_tree()

Все табличные функции json_each(X), jsonb_each(X), json_tree(X) и json_treeb(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:

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

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.

Столбец "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. Для массива или объекта JSON столбец "atom" содержит NULL.

Столбец "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', которое может быть глубоко вложенным объектом. Требуется найти идентификатор каждой записи, в поле '$.partlist' которой где-либо содержится одна или несколько ссылок на uuid '6fa5181e-5721-11e5-a04e-57f3d7b32808'.

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