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

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

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

json(json)
jsonb(json)
json_array(value1,value2,...)
jsonb_array(value1,value2,...)
json_array_length(json)
json_array_length(json,path)
json_error_position(json)
json_extract(json,path,...)
jsonb_extract(json,path,...)
json -> path
json ->> path
json_insert(json,path,value,...)
jsonb_insert(json,path,value,...)
json_object(label1,value1,...)
jsonb_object(label1,value1,...)
json_patch(json1,json2)
jsonb_patch(json1,json2)
json_pretty(json)
json_remove(json,path,...)
jsonb_remove(json,path,...)
json_replace(json,path,value,...)
jsonb_replace(json,path,value,...)
json_set(json,path,value,...)
jsonb_set(json,path,value,...)
json_type(json)
json_type(json,path)
json_valid(json)
json_valid(json,flags)
json_quote(value)

Есть четыре агрегирующие SQL функции:

json_group_array(value)
jsonb_group_array(value)
json_group_object(label,value)
jsonb_group_object(name,value)

Две таблично-значимые функции:

json_each(json)
json_each(json,path)
json_tree(json)
json_tree(json,path)

2. Поддержка JSON при компиляции

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

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

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

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

Для функций, принимающих JSON в качестве первого аргумента, этот аргумент может быть объектом JSON, массивом, числом, строкой или null. Значения SQLite числового типа и NULL интерпретируются как числа JSON и null соответственно. Значения 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 напрямую в базе данных позволяет приложениям обойти накладные расходы на парсинг и рендеринг 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. У форматов одинаковое название, но они не двоично совместимы. Формат PostgreSQL JSONB утверждает, что обеспечивает O(1) поиск элементов в объектах и массивах. Формат JSONB SQLite не делает такого утверждения. JSONB в SQLite имеет сложность O(N) для большинства операций в SQLite, как и текстовый JSON. Преимущество JSONB в SQLite заключается в том, что он меньше и быстрее, чем текстовый JSON — потенциально в несколько раз быстрее. В формате JSONB на диске есть место для улучшений, и в будущих версиях SQLite могут появиться параметры для обеспечения O(1) поиска элементов в JSONB, но такой возможности в настоящее время нет.

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

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

SQL-запрос может прерваться с ошибкой «неправильно сформированный JSON».
Может быть возвращен правильный ответ, если неправильно сформированные части BLOB JSONB не повлияют на ответ.
Может быть возвращен глупый или бессмысленный ответ.

Способ, которым SQLite обрабатывает недействительный 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):

Ключи объектов могут быть нецитируемыми идентификаторами.
Объекты могут иметь одинокую заключительную запятую.
Массивы могут иметь одинокую заключительную запятую.
Строки могут быть заключены в одинарные кавычки.
Строки могут занимать несколько строк, экранируя символы новой строки.
Строки могут включать экранирования новых символов.
Числа могут быть шестнадцатеричными.
Числа могут иметь ведущую или заключительную десятичную точку.
Числа могут быть «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. Но для определения того, является ли ключ именем идентификатора ECMAScript 5.1, требуется большая таблица 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 в дикой природе, включающих эти нестандартные представления бесконечности и нечисловых значений.

3.7. Учет производительности

Большинство 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-строкой в текстовом кодировании базы данных.

Эта ошибка ввода 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) и всех последующих выпусках.

4. Подробные сведения о функциях

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

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

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

Если входной текст — 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, то эта функция просто возвращает копию 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]"]'
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_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.6. Функция json_error_position()

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

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

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

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

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

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

Операция	Результат SQLite	Результат MySQL
json_extract('{"a":null,"b":"xyz"}','$.a')	NULL	'null'
json_extract('{"a":null,"b":"xyz"}','$.b')	'xyz'	'"xyz"'

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

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

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

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

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

Когда оператор -> возвращает JSON, он всегда возвращает текстовое представление RFC 8565 этого JSON, а не 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.10. Функции json_insert(), json_replace и json_set()

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

Функция	Перезапись, если уже существует?	Создать, если не существует?
json_insert()	Нет	Да
json_replace()	Да	Нет
json_set()	Да	Да

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

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

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

Эти функции вызывают ошибку, если первый аргумент JSON не соответствует формату, если любой аргумент "путь" не соответствует формату или если какой-либо аргумент является 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.11. Функции jsonb_insert(), jsonb_replace и jsonb_set()

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

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

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

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

Аргумент типа TEXT SQL обычно преобразуется в строку 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.13. Функция jsonb_object()

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

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

Функция json_patch(T,P) SQL выполняет алгоритм 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.15. Функция jsonb_patch()

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

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

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

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

4.17. Функция 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

END_OF_DOCUMENT_MARKER

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

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

4.19. Функция 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.20. Функция json_valid()

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

0x01 → Ввод — это текст, который строго соответствует каноническому RFC-8259 JSON без каких-либо расширений.
0x02 → Ввод — это текст, который является JSON с расширениями JSON5, описанными выше.
0x04 → Ввод — это BLOB, который внешне выглядит как JSONB.
0x08 → Ввод — это BLOB, который строго соответствует внутреннему формату JSONB.

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

1 → X — текстовый RFC-8259 JSON
2 → X — текстовый JSON5
4 → X, вероятно, JSONB
5 → X — текстовый RFC-8259 JSON или JSONB
6 → X — текстовый JSON5 или JSONB ← Вероятно, это значение вам нужно
8 → X — строго соответствующий JSONB
9 → X — RFC-8259 или строго соответствующий JSONB
10 → X — JSON5 или строго соответствующий JSONB

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

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

4.23. Таблично-значные функции 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
);

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

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

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

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

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

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

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

4.23.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', которое может быть глубоко вложенным объектом. Вы хотите найти 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';

Эта страница была последним обновлена 18 сентября 2024 г. в 15:57:24 UTC

SQLite is in the Public Domain.
https://sqlite.org/json1.html