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

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

Важно отметить, что стандарт SQL не содержит типа данных JSON. Вместо этого данные JSON представляются как символьные или двоичные строки. Хотя CedrusData поддерживает тип JSON, он не используется и не создаётся следующими функциями.

CedrusData поддерживает три функции для запросов к данным JSON: json_exists, json_query и json_value. Каждая из них основана на одном и том же механизме обработки входных данных JSON с использованием JSON path.

CedrusData также поддерживает две функции для генерации данных JSON — json_array и json_object.

Язык JSON path#

JSON path — это специальный язык, используемый определёнными для указания запроса, выполняемого над входными данными JSON. Синтаксис JSON path значительно отличается от SQL. Семантика предикатов, операторов и т.д. в выражениях JSON path в целом следует семантике SQL. Язык JSON path является чувствительным к регистру для ключевых слов и идентификаторов.

Синтаксис и семантика JSON path#

Выражение JSON path является рекурсивной древовидной структурой. Оно может обращаться к входному элементу JSON множество раз и комбинировать результаты. Результатом выражения JSON path является упорядоченная последовательность элементов. Каждое подвыражение принимает одну или несколько входных последовательностей и возвращает другую последовательность в качестве результата.

JSON path поддерживает литералы, переменные, арифметические бинарные выражения, арифметические унарные выражения и accessors (назначение которых будет объяснено ниже).

Литералы#

  • Числовые литералы

    Включают точные и приближённые числа и интерпретируются так, как если бы они были значениями SQL.

-1, 1.2e3, NaN

  • Строковые литералы

    Заключаются в двойные кавычки.

"Some text"

  • Логические литералы

true, false

null

Переменные#

  • Контекстная переменная

    Ссылается на текущий обрабатываемый вход функции JSON.

$

  • Именованная переменная

    Ссылается на именованный параметр по его имени.

$param

  • Переменная текущего элемента

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

@

  • Переменная последнего индекса

    Ссылается на последний индекс ближайшего охватывающего массива. Индексы массивов в выражениях JSON path начинаются с нуля.

last

Арифметические бинарные выражения#

Язык JSON path поддерживает пять арифметических бинарных операторов:

<path1> + <path2>
<path1> - <path2>
<path1> * <path2>
<path1> / <path2>
<path1> % <path2>

Результатом обработки операндов <path1> и <path2> являются последовательности элементов. Для арифметических бинарных операторов каждая входная последовательность должна содержать один числовой элемент. Арифметическая операция выполняется в соответствии с семантикой SQL и возвращает последовательность, содержащую один элемент с результатом.

Операторы следуют тем же правилам приоритета, что и арифметические операции SQL. Для группировки можно использовать круглые скобки.

Арифметические унарные выражения#

+ <path>
- <path>

Операнд <path> вычисляется в последовательность элементов. Каждый элемент последовательности должен быть числовым значением. Унарный плюс или минус применяется к каждому элементу последовательности в соответствии с семантикой SQL.

Accessor поля объекта#

Accessor поля объекта возвращает значение поля с указанным ключом для каждого объекта JSON во входной последовательности.

<path>.key
<path>."key"

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

Пусть <path> возвращает последовательность из трёх объектов JSON:

{"customer" : 100, "region" : "AFRICA"},
{"region" : "ASIA"},
{"customer" : 300, "region" : "AFRICA", "comment" : null}

Выражение <path>.customer будет успешно выполнено для первого и третьего объектов, но у второго объекта отсутствует требуемое поле. В строгом режиме вычисление пути завершается ошибкой. В нестрогом режиме второй объект будет пропущен, результирующая последовательность — 100, 300.

Все элементы во входной последовательности должны быть объектами JSON.

Примечание

CedrusData не поддерживает объекты JSON с дублирующими ключами.

Accessor ко всем полям объекта#

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

<path>.*

Пусть <path> возвращает последовательность из трёх объектов JSON:

{"customer" : 100, "region" : "AFRICA"},
{"region" : "ASIA"},
{"customer" : 300, "region" : "AFRICA", "comment" : null}

Результат:

100, "AFRICA", "ASIA", 300, "AFRICA", null

Все элементы во входной последовательности должны быть объектами JSON.

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

Accessor к потомкам#

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

<path>..key
<path>.."key"

Порядок возвращаемых значений соответствует обходу в глубину. Сначала посещается родительский объект, а затем все дочерние узлы.

Этот метод не выполняет разворачивание массивов в нестрогом режиме. Результаты одинаковы в нестрогом и строгом режимах. Метод проходит через массивы JSON и объекты JSON. Неструктурные элементы JSON пропускаются.

Пусть <path> — последовательность, содержащая объект JSON:

{
    "id" : 1,
    "notes" : [{"type" : 1, "comment" : "foo"}, {"type" : 2, "comment" : null}],
    "comment" : ["bar", "baz"]
}

<path>..comment --> ["bar", "baz"], "foo", null

Accessor к элементам массива#

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

<path>[ <subscripts> ]

Список <subscripts> содержит один или более индексов. Каждый индекс задаёт одиночный индекс или диапазон (границы включительно):

<path>[<path1>, <path2> to <path3>, <path4>,...]

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

Каждый массив во входной последовательности обрабатывается следующим образом:

  • Переменная last устанавливается в значение последнего индекса массива.

  • Все индексы вычисляются в порядке объявления. Для одиночного индекса <path1> результатом должен быть единственный числовой элемент. Для диапазонного индекса <path2> to <path3> ожидаются два числовых элемента.

  • Указанные элементы массива добавляются по порядку в выходную последовательность.

Пусть <path> возвращает последовательность из трёх массивов JSON:

[0, 1, 2], ["a", "b", "c", "d"], [null, null]

Следующее выражение возвращает последовательность, содержащую последний элемент каждого массива:

<path>[last] --> 2, "d", null

Следующее выражение возвращает третий и четвёртый элементы каждого массива:

<path>[2 to 3] --> 2, "c", "d"

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

Другой пример структурной ошибки — некорректная спецификация диапазона, такая как 5 to 3.

Обратите внимание, что индексы могут повторяться и не обязаны следовать порядку элементов. Порядок в возвращаемой последовательности соответствует индексам:

<path>[1, 0, 0] --> 1, 0, 0, "b", "a", "a", null, null, null

Accessor ко всем элементам массива#

Возвращает все элементы каждого массива JSON во входной последовательности.

<path>[*]

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

Порядок на выходе соответствует порядку исходных массивов JSON. Также сохраняется порядок элементов внутри массивов.

Пусть <path> возвращает последовательность из трёх массивов JSON:

[0, 1, 2], ["a", "b", "c", "d"], [null, null]
<path>[*] --> 0, 1, 2, "a", "b", "c", "d", null, null

Фильтр#

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

<path>?( <predicate> )

Предикаты JSON path синтаксически похожи на логические выражения в SQL. Однако семантика отличается во многих аспектах:

  • Они оперируют последовательностями элементов.

  • Они никогда не завершаются с ошибкой.

  • Они ведут себя по-разному в зависимости от нестрогого или строгого режима.

Предикат вычисляется в true, false или unknown. Обратите внимание, что некоторые предикатные выражения включают вложенные выражения JSON path. При вычислении вложенного пути переменная @ ссылается на текущий проверяемый элемент из входной последовательности.

Поддерживаются следующие предикаты:

  • Конъюнкция

<predicate1> && <predicate2>

  • Дизъюнкция

<predicate1> || <predicate2>

  • Отрицание

! <predicate>

  • Предикат exists

exists( <path> )

Возвращает true, если вложенный путь вычисляется в непустую последовательность, и false, когда вложенный путь вычисляется в пустую последовательность. Если вычисление пути завершается ошибкой, возвращает unknown.

  • Предикат starts with

<path> starts with "Some text"
<path> starts with $variable

Вложенный <path> должен вычисляться в последовательность текстовых элементов, а другой операнд должен вычисляться в один текстовый элемент. Если вычисление любого из операндов завершается ошибкой, результат — unknown. Все элементы последовательности проверяются на то, начинаются ли они с правого операнда. Результат — true, если совпадение найдено, иначе false. Однако если любое из сравнений завершается ошибкой, результат в строгом режиме — unknown. Результат в нестрогом режиме зависит от того, что было найдено первым — совпадение или ошибка.

  • Предикат is unknown

( <predicate> ) is unknown

Возвращает true, если вложенный предикат вычисляется в unknown, и false в противном случае.

  • Сравнения

<path1> == <path2>
<path1> <> <path2>
<path1> != <path2>
<path1> < <path2>
<path1> > <path2>
<path1> <= <path2>
<path1> >= <path2>

Оба операнда сравнения вычисляются в последовательности элементов. Если любое из вычислений завершается ошибкой, результат — unknown. Элементы из левой и правой последовательностей затем сравниваются попарно. Аналогично предикату starts with, результат — true, если любое из сравнений возвращает true, иначе false. Однако если любое из сравнений завершается ошибкой, например из-за несовместимости сравниваемых типов, результат в строгом режиме — unknown. Результат в нестрогом режиме зависит от того, что было найдено первым — сравнение с результатом true или ошибка.

Правила сравнения#

Значения null в контексте сравнения ведут себя иначе, чем SQL null:

  • null == null –> true

  • null != null, null < null, … –> false

  • null в сравнении со скалярным значением –> false

  • null в сравнении с массивом JSON или объектом JSON –> false

При сравнении двух скалярных значений возвращается true или false, если сравнение выполнено успешно. Семантика сравнения такая же, как в SQL. В случае ошибки, например при сравнении текста и числа, возвращается unknown.

Сравнение скалярного значения с массивом JSON или объектом JSON, а также сравнение массивов/объектов JSON является ошибкой, поэтому возвращается unknown.

Примеры фильтров#

Пусть <path> возвращает последовательность из трёх объектов JSON:

{"customer" : 100, "region" : "AFRICA"},
{"region" : "ASIA"},
{"customer" : 300, "region" : "AFRICA", "comment" : null}

<path>?(@.region != "ASIA") --> {"customer" : 100, "region" : "AFRICA"},
                                {"customer" : 300, "region" : "AFRICA", "comment" : null}
<path>?(!exists(@.customer)) --> {"region" : "ASIA"}

Следующие accessors совокупно называются методами элементов.

double()#

Преобразует числовые или текстовые значения в значения типа double.

<path>.double()

Пусть <path> возвращает последовательность -1, 23e4, "5.6":

<path>.double() --> -1e0, 23e4, 5.6e0

ceiling(), floor() и abs()#

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

Пусть <path> возвращает последовательность -1.5, -1, 1.3:

<path>.ceiling() --> -1.0, -1, 2.0
<path>.floor() --> -2.0, -1, 1.0
<path>.abs() --> 1.5, 1, 1.3

keyvalue()#

Возвращает коллекцию объектов JSON, включающую один объект для каждого поля исходного объекта, для каждого объекта JSON в последовательности.

<path>.keyvalue()

Возвращаемые объекты имеют три поля:

  • «name» — исходный ключ,

  • «value» — исходное привязанное значение,

  • «id» — уникальный номер, специфичный для входного объекта.

Пусть <path> — последовательность из трёх объектов JSON:

{"customer" : 100, "region" : "AFRICA"},
{"region" : "ASIA"},
{"customer" : 300, "region" : "AFRICA", "comment" : null}

<path>.keyvalue() --> {"name" : "customer", "value" : 100, "id" : 0},
                      {"name" : "region", "value" : "AFRICA", "id" : 0},
                      {"name" : "region", "value" : "ASIA", "id" : 1},
                      {"name" : "customer", "value" : 300, "id" : 2},
                      {"name" : "region", "value" : "AFRICA", "id" : 2},
                      {"name" : "comment", "value" : null, "id" : 2}

Требуется, чтобы все элементы во входной последовательности были объектами JSON.

Порядок возвращаемых значений следует порядку исходных объектов JSON. Однако внутри объектов порядок возвращаемых записей произволен.

type()#

Возвращает текстовое значение, содержащее имя типа для каждого элемента в последовательности.

<path>.type()

Этот метод не выполняет разворачивание массивов в нестрогом режиме.

Возвращаемые значения:

  • "null" для JSON null,

  • "number" для числового элемента,

  • "string" для текстового элемента,

  • "boolean" для логического элемента,

  • "date" для элемента типа date,

  • "time without time zone" для элемента типа time,

  • "time with time zone" для элемента типа time with time zone,

  • "timestamp without time zone" для элемента типа timestamp,

  • "timestamp with time zone" для элемента типа timestamp with time zone,

  • "array" для массива JSON,

  • "object" для объекта JSON.

size()#

Возвращает числовое значение, содержащее размер для каждого массива JSON в последовательности.

<path>.size()

Этот метод не выполняет разворачивание массивов в нестрогом режиме. Вместо этого все элементы, не являющиеся массивами, оборачиваются в одноэлементные массивы JSON, поэтому их размер равен 1.

Требуется, чтобы все элементы во входной последовательности были массивами JSON.

Пусть <path> возвращает последовательность из трёх массивов JSON:

[0, 1, 2], ["a", "b", "c", "d"], [null, null]
<path>.size() --> 3, 4, 2

Ограничения#

Стандарт SQL описывает метод элемента JSON path datetime() и предикат JSON path like_regex(). CedrusData не поддерживает их.

Режимы JSON path#

Выражение JSON path может вычисляться в двух режимах: строгом и нестрогом. В строгом режиме требуется, чтобы входные данные JSON строго соответствовали схеме, требуемой выражением пути. В нестрогом режиме входные данные JSON могут отклоняться от ожидаемой схемы.

Следующая таблица показывает различия между двумя режимами.

Условие

Строгий режим

Нестрогий режим

Выполнение операции, требующей не-массив, над массивом, например:

$.key требует объект JSON

$.floor() требует числовое значение

ОШИБКА

Массив автоматически разворачивается, и операция выполняется над каждым элементом массива.

Выполнение операции, требующей массив, над не-массивом, например:

$[0], $[*], $.size()

ОШИБКА

Элемент, не являющийся массивом, автоматически оборачивается в одноэлементный массив, и операция выполняется над массивом.

Структурная ошибка: обращение к несуществующему элементу массива или несуществующему полю объекта JSON, например:

$[-1] (индекс массива выходит за границы)

$.key, где входной объект JSON не имеет поля key

ОШИБКА

Ошибка подавляется, и операция возвращает пустую последовательность.

Примеры поведения нестрогого режима#

Пусть <path> возвращает последовательность из трёх элементов: массив JSON, объект JSON и скалярное числовое значение:

[1, "a", null], {"key1" : 1.0, "key2" : true}, -2e3

Следующий пример демонстрирует accessor ко всем элементам массива в нестрогом режиме. Массив JSON возвращает все свои элементы, а объект JSON и число оборачиваются в одноэлементные массивы и затем разворачиваются, поэтому фактически они появляются без изменений в выходной последовательности:

<path>[*] --> 1, "a", null, {"key1" : 1.0, "key2" : true}, -2e3

При вызове метода size() объект JSON и число также оборачиваются в одноэлементные массивы:

<path>.size() --> 3, 1, 1

В некоторых случаях нестрогий режим не может предотвратить ошибку. В следующем примере, несмотря на то что массив JSON разворачивается перед вызовом метода floor(), элемент "a" вызывает несоответствие типов.

<path>.floor() --> ERROR

json_exists#

Функция json_exists определяет, удовлетворяет ли значение JSON спецификации JSON path.

JSON_EXISTS(
    json_input [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ],
    json_path
    [ PASSING json_argument [, ...] ]
    [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]
    )

Путь json_path вычисляется с использованием json_input в качестве контекстной переменной ($) и переданных аргументов в качестве именованных переменных ($variable_name). Возвращаемое значение — true, если путь возвращает непустую последовательность, и false, если путь возвращает пустую последовательность. Если возникает ошибка, возвращаемое значение зависит от условия ON ERROR. Значение по умолчанию, возвращаемое ON ERROR, — FALSE. Условие ON ERROR применяется для следующих видов ошибок:

  • Ошибки преобразования входных данных, такие как некорректный JSON

  • Ошибки вычисления JSON path, например деление на ноль.

json_input — символьная строка или двоичная строка. Она должна содержать один элемент JSON. Для двоичной строки можно указать кодировку.

json_path — строковый литерал, содержащий спецификацию режима пути и выражение пути в соответствии с правилами синтаксиса, описанными в Синтаксис и семантика JSON path.

'strict ($.price + $.tax)?(@ > 99.9)'
'lax $[0 to 1].floor()?(@ > 10)'

В условии PASSING можно передавать произвольные выражения для использования в выражении пути.

PASSING orders.totalprice AS O_PRICE,
        orders.tax % 10 AS O_TAX

Переданные параметры могут быть указаны в выражении пути с помощью именованных переменных с префиксом $.

'lax $?(@.price > $O_PRICE || @.tax > $O_TAX)'

Помимо значений SQL, можно передавать значения JSON, указывая формат и необязательную кодировку:

PASSING orders.json_desc FORMAT JSON AS o_desc,
        orders.binary_record FORMAT JSON ENCODING UTF16 AS o_rec

Обратите внимание, что язык JSON path чувствителен к регистру, тогда как идентификаторы SQL без кавычек приводятся к верхнему регистру. Поэтому рекомендуется использовать идентификаторы в кавычках в условии PASSING:

'lax $.keyvalue()?(@.name == $KeyName).value' PASSING nation.name AS KeyName --> ERROR; no passed value found
'lax $.keyvalue()?(@.name == $KeyName).value' PASSING nation.name AS "KeyName" --> correct

Примеры#

Пусть customers — таблица с двумя столбцами: id:bigint, description:varchar.

id

description

101

„{«comment» : «nice», «children» : [10, 13, 16]}“

102

„{«comment» : «problematic», «children» : [8, 11]}“

103

„{«comment» : «knows best», «children» : [2]}“

Следующий запрос проверяет, у каких клиентов есть дети старше 10 лет:

SELECT
      id,
      json_exists(
                  description,
                  'lax $.children[*]?(@ > 10)'
                 ) AS children_above_ten
FROM customers

id

children_above_ten

101

true

102

true

103

false

В следующем запросе используется строгий режим пути. Мы проверяем третьего ребёнка каждого клиента. Это должно вызвать структурную ошибку для клиентов, у которых менее трёх детей. Эта ошибка обрабатывается в соответствии с условием ON ERROR.

SELECT
      id,
      json_exists(
                  description,
                  'strict $.children[2]?(@ > 10)'
                  UNKNOWN ON ERROR
                 ) AS child_3_above_ten
FROM customers

id

child_3_above_ten

101

true

102

NULL

103

NULL

json_query#

Функция json_query извлекает значение JSON из значения JSON.

JSON_QUERY(
    json_input [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ],
    json_path
    [ PASSING json_argument [, ...] ]
    [ RETURNING type [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ] ]
    [ WITHOUT [ ARRAY ] WRAPPER |
      WITH [ { CONDITIONAL | UNCONDITIONAL } ] [ ARRAY ] WRAPPER ]
    [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
    [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON EMPTY ]
    [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON ERROR ]
    )

Константная строка json_path вычисляется с использованием json_input в качестве контекстной переменной ($) и переданных аргументов в качестве именованных переменных ($variable_name).

Возвращаемое значение — элемент JSON, возвращённый путём. По умолчанию он представляется как символьная строка (varchar). В условии RETURNING можно указать другой тип символьной строки или varbinary. Для varbinary можно также указать желаемую кодировку.

json_input — символьная строка или двоичная строка. Она должна содержать один элемент JSON. Для двоичной строки можно указать кодировку.

json_path — строковый литерал, содержащий спецификацию режима пути и выражение пути в соответствии с правилами синтаксиса, описанными в Синтаксис и семантика JSON path.

'strict $.keyvalue()?(@.name == $cust_id)'
'lax $[5 to last]'

В условии PASSING можно передавать произвольные выражения для использования в выражении пути.

PASSING orders.custkey AS CUST_ID

Переданные параметры могут быть указаны в выражении пути с помощью именованных переменных с префиксом $.

'strict $.keyvalue()?(@.value == $CUST_ID)'

Помимо значений SQL, можно передавать значения JSON, указывая формат и необязательную кодировку:

PASSING orders.json_desc FORMAT JSON AS o_desc,
        orders.binary_record FORMAT JSON ENCODING UTF16 AS o_rec

Обратите внимание, что язык JSON path чувствителен к регистру, тогда как идентификаторы SQL без кавычек приводятся к верхнему регистру. Поэтому рекомендуется использовать идентификаторы в кавычках в условии PASSING:

'lax $.keyvalue()?(@.name == $KeyName).value' PASSING nation.name AS KeyName --> ERROR; no passed value found
'lax $.keyvalue()?(@.name == $KeyName).value' PASSING nation.name AS "KeyName" --> correct

Условие ARRAY WRAPPER позволяет модифицировать результат, оборачивая его в массив JSON. WITHOUT ARRAY WRAPPER — опция по умолчанию. WITH CONDITIONAL ARRAY WRAPPER оборачивает каждый результат, который не является одиночным массивом JSON или объектом JSON. WITH UNCONDITIONAL ARRAY WRAPPER оборачивает каждый результат.

Условие QUOTES позволяет модифицировать результат для скалярной строки, удаляя двойные кавычки, являющиеся частью представления строки JSON.

Примеры#

Пусть customers — таблица с двумя столбцами: id:bigint, description:varchar.

id

description

101

„{«comment» : «nice», «children» : [10, 13, 16]}“

102

„{«comment» : «problematic», «children» : [8, 11]}“

103

„{«comment» : «knows best», «children» : [2]}“

Следующий запрос получает массив children для каждого клиента:

SELECT
      id,
      json_query(
                 description,
                 'lax $.children'
                ) AS children
FROM customers

id

children

101

„[10,13,16]“

102

„[8,11]“

103

„[2]“

Следующий запрос получает коллекцию детей для каждого клиента. Обратите внимание, что функция json_query может выводить только один элемент JSON. Если не использовать обёртку массива, для каждого клиента с несколькими детьми возникнет ошибка. Ошибка обрабатывается в соответствии с условием ON ERROR.

SELECT
      id,
      json_query(
                 description,
                 'lax $.children[*]'
                 WITHOUT ARRAY WRAPPER
                 NULL ON ERROR
                ) AS children
FROM customers

id

children

101

NULL

102

NULL

103

„2“

Следующий запрос получает последнего ребёнка для каждого клиента, обёрнутого в массив JSON:

SELECT
      id,
      json_query(
                 description,
                 'lax $.children[last]'
                 WITH ARRAY WRAPPER
                ) AS last_child
FROM customers

id

last_child

101

„[16]“

102

„[11]“

103

„[2]“

Следующий запрос получает всех детей старше 12 лет для каждого клиента, обёрнутых в массив JSON. У второго и третьего клиентов нет детей этого возраста. Такой случай обрабатывается в соответствии с условием ON EMPTY. Значение по умолчанию, возвращаемое ON EMPTY — NULL. В следующем примере указано EMPTY ARRAY ON EMPTY.

SELECT
      id,
      json_query(
                 description,
                 'strict $.children[*]?(@ > 12)'
                 WITH ARRAY WRAPPER
                 EMPTY ARRAY ON EMPTY
                ) AS children
FROM customers

id

children

101

„[13,16]“

102

„[]“

103

„[]“

Следующий запрос демонстрирует результат условия QUOTES. Обратите внимание, что KEEP QUOTES используется по умолчанию.

SELECT
      id,
      json_query(description, 'strict $.comment' KEEP QUOTES) AS quoted_comment,
      json_query(description, 'strict $.comment' OMIT QUOTES) AS unquoted_comment
FROM customers

id

quoted_comment

unquoted_comment

101

„«nice»“

„nice“

102

„«problematic»“

„problematic“

103

„«knows best»“

„knows best“

Если возникает ошибка, возвращаемое значение зависит от условия ON ERROR. Значение по умолчанию, возвращаемое ON ERROR — NULL. Одним из примеров ошибки является возврат множества элементов путём. Другие ошибки, перехватываемые и обрабатываемые в соответствии с условием ON ERROR:

  • Ошибки преобразования входных данных, такие как некорректный JSON

  • Ошибки вычисления JSON path, например деление на ноль

  • Ошибки преобразования выходных данных

json_value#

Функция json_value извлекает скалярное значение SQL из значения JSON.

JSON_VALUE(
    json_input [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ],
    json_path
    [ PASSING json_argument [, ...] ]
    [ RETURNING type ]
    [ { ERROR | NULL | DEFAULT expression } ON EMPTY ]
    [ { ERROR | NULL | DEFAULT expression } ON ERROR ]
    )

Путь json_path вычисляется с использованием json_input в качестве контекстной переменной ($) и переданных аргументов в качестве именованных переменных ($variable_name).

Возвращаемое значение — скаляр SQL, возвращённый путём. По умолчанию он преобразуется в строку (varchar). В условии RETURNING можно указать другой желаемый тип: символьный строковый тип, числовой, логический или тип даты/времени.

json_input — символьная строка или двоичная строка. Она должна содержать один элемент JSON. Для двоичной строки можно указать кодировку.

json_path — строковый литерал, содержащий спецификацию режима пути и выражение пути в соответствии с правилами синтаксиса, описанными в Синтаксис и семантика JSON path.

'strict $.price + $tax'
'lax $[last].abs().floor()'

В условии PASSING можно передавать произвольные выражения для использования в выражении пути.

PASSING orders.tax AS O_TAX

Переданные параметры могут быть указаны в выражении пути с помощью именованных переменных с префиксом $.

'strict $[last].price + $O_TAX'

Помимо значений SQL, можно передавать значения JSON, указывая формат и необязательную кодировку:

PASSING orders.json_desc FORMAT JSON AS o_desc,
        orders.binary_record FORMAT JSON ENCODING UTF16 AS o_rec

Обратите внимание, что язык JSON path чувствителен к регистру, тогда как идентификаторы SQL без кавычек приводятся к верхнему регистру. Поэтому рекомендуется использовать идентификаторы в кавычках в условии PASSING:

'lax $.keyvalue()?(@.name == $KeyName).value' PASSING nation.name AS KeyName --> ERROR; no passed value found
'lax $.keyvalue()?(@.name == $KeyName).value' PASSING nation.name AS "KeyName" --> correct

Если путь возвращает пустую последовательность, применяется условие ON EMPTY. Значение по умолчанию, возвращаемое ON EMPTY — NULL. Также можно указать значение по умолчанию:

DEFAULT -1 ON EMPTY

Если возникает ошибка, возвращаемое значение зависит от условия ON ERROR. Значение по умолчанию, возвращаемое ON ERROR — NULL. Одним из примеров ошибки является возврат множества элементов путём. Другие ошибки, перехватываемые и обрабатываемые в соответствии с условием ON ERROR:

  • Ошибки преобразования входных данных, такие как некорректный JSON

  • Ошибки вычисления JSON path, например деление на ноль

  • Невозможность преобразования возвращённого скаляра в желаемый тип.

Примеры#

Пусть customers — таблица с двумя столбцами: id:bigint, description:varchar.

id

description

101

„{«comment» : «nice», «children» : [10, 13, 16]}“

102

„{«comment» : «problematic», «children» : [8, 11]}“

103

„{«comment» : «knows best», «children» : [2]}“

Следующий запрос получает значение comment для каждого клиента в виде char(12):

SELECT id, json_value(
                      description,
                      'lax $.comment'
                      RETURNING char(12)
                     ) AS comment
FROM customers

id

comment

101

„nice „

102

„problematic „

103

„knows best „

Следующий запрос получает возраст первого ребёнка каждого клиента в виде tinyint:

SELECT id, json_value(
                      description,
                      'lax $.children[0]'
                      RETURNING tinyint
                     ) AS child
FROM customers

id

child

101

10

102

8

103

2

Следующий запрос получает возраст третьего ребёнка каждого клиента. В строгом режиме это должно вызвать структурную ошибку для клиентов, у которых нет третьего ребёнка. Эта ошибка обрабатывается в соответствии с условием ON ERROR.

SELECT id, json_value(
                      description,
                      'strict $.children[2]'
                      DEFAULT 'err' ON ERROR
                     ) AS child
FROM customers

id

child

101

„16“

102

„err“

103

„err“

После переключения в нестрогий режим структурная ошибка подавляется, и клиенты без третьего ребёнка возвращают пустую последовательность. Такой случай обрабатывается в соответствии с условием ON EMPTY.

SELECT id, json_value(
                      description,
                      'lax $.children[2]'
                      DEFAULT 'missing' ON EMPTY
                     ) AS child
FROM customers

id

child

101

„16“

102

„missing“

103

„missing“

json_table#

Условие json_table извлекает таблицу из значения JSON. Используйте это условие для преобразования данных JSON в реляционный формат, что упрощает выполнение запросов и анализ. Используйте json_table в условии FROM оператора SELECT для создания таблицы из данных JSON.

JSON_TABLE(
    json_input,
    json_path [ AS path_name ]
    [ PASSING value AS parameter_name [, ...] ]
    COLUMNS (
        column_definition [, ...] )
    [ PLAN ( json_table_specific_plan )
      | PLAN DEFAULT ( json_table_default_plan ) ]
    [ { ERROR | EMPTY } ON ERROR ]
)

Условие COLUMNS поддерживает следующие аргументы column_definition:

column_name FOR ORDINALITY
| column_name type
    [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ]
    [ PATH json_path ]
    [ { WITHOUT | WITH { CONDITIONAL | UNCONDITIONAL } } [ ARRAY ] WRAPPER ]
    [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
    [ { ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULT expression } ON EMPTY ]
    [ { ERROR | NULL | DEFAULT expression } ON ERROR ]
| NESTED [ PATH ] json_path [ AS path_name ] COLUMNS ( column_definition [, ...] )

json_input — символьная строка или двоичная строка. Она должна содержать один элемент JSON.

json_path — строковый литерал, содержащий спецификацию режима пути и выражение пути. Он следует правилам синтаксиса, описанным в Синтаксис и семантика JSON path.

'strict ($.price + $.tax)?(@ > 99.9)'
'lax $[0 to 1].floor()?(@ > 10)'

В условии PASSING передавайте значения как именованные параметры, на которые может ссылаться выражение json_path.

PASSING orders.totalprice AS o_price,
        orders.tax % 10 AS o_tax

Используйте именованные параметры для ссылки на значения в выражении пути. Именованные параметры имеют префикс $.

'lax $?(@.price > $o_price || @.tax > $o_tax)'

Вы также можете передавать значения JSON в условии PASSING. Используйте FORMAT JSON для указания формата и ENCODING для указания кодировки:

PASSING orders.json_desc FORMAT JSON AS o_desc,
        orders.binary_record FORMAT JSON ENCODING UTF16 AS o_rec

Значение json_path чувствительно к регистру. Идентификаторы SQL приводятся к верхнему регистру. Используйте идентификаторы в кавычках в условии PASSING:

'lax $.keyvalue()?(@.name == $KeyName).value' PASSING nation.name AS KeyName --> ERROR; no passed value found
'lax $.keyvalue()?(@.name == $KeyName).value' PASSING nation.name AS "KeyName" --> correct

Условие PLAN определяет, как объединять столбцы из разных путей. Используйте OUTER или INNER для определения способа объединения родительских путей с дочерними. Используйте CROSS или UNION для объединения одноуровневых путей.

COLUMNS определяет схему вашей таблицы. Каждое определение column_definition указывает, как извлечь и отформатировать значение json_input в реляционный столбец.

PLAN — необязательное условие для управления обработкой и объединением вложенных данных JSON.

ON ERROR определяет, как обрабатывать ошибки обработки. ERROR ON ERROR вызывает ошибку. EMPTY ON ERROR возвращает пустой набор результатов.

column_name задаёт имя столбца.

FOR ORDINALITY добавляет столбец с номером строки в выходную таблицу, начиная с 1. Укажите имя столбца в определении столбца:

row_num FOR ORDINALITY

NESTED PATH извлекает данные из вложенных уровней значения json_input. Каждое условие NESTED PATH может содержать значения column_definition.

Функция json_table возвращает набор результатов, который можно использовать как любую другую таблицу в запросах. Вы можете объединять набор результатов с другими таблицами или комбинировать несколько массивов из ваших данных JSON.

Вы также можете обрабатывать вложенные объекты JSON без повторного разбора данных.

Используйте json_table как латеральное соединение для обработки данных JSON из другой таблицы.

Примеры#

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

SELECT
      *
FROM
      json_table(
                '[
                  {"id":1,"name":"Africa","wikiDataId":"Q15"},
                  {"id":2,"name":"Americas","wikiDataId":"Q828"},
                  {"id":3,"name":"Asia","wikiDataId":"Q48"},
                  {"id":4,"name":"Europe","wikiDataId":"Q51"}
                ]',
                'strict $' COLUMNS (
                  NESTED PATH 'strict $[*]' COLUMNS (
                    id integer PATH 'strict $.id',
                    name varchar PATH 'strict $.name',
                    wiki_data_id varchar PATH 'strict $."wikiDataId"'
                  )
                )
              );

id

child

wiki_data_id

1

Africa

Q1

2

Americas

Q828

3

Asia

Q48

4

Europe

Q51

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

Условие NESTED PATH 'lax $[*]' выполняет итерацию по объектам континентов, а NESTED PATH 'lax $.countries[*]' — по каждой стране внутри каждого континента. В результате создаётся плоская структура таблицы с четырьмя строками, объединяющая каждый континент с каждой из его стран. Значения континентов повторяются для каждой из их стран.

SELECT
      *
FROM
      json_table(
                '[
                    {"continent": "Asia", "countries": [
                        {"name": "Japan", "population": 125.7},
                        {"name": "Thailand", "population": 71.6}
                    ]},
                    {"continent": "Europe", "countries": [
                        {"name": "France", "population": 67.4},
                        {"name": "Germany", "population": 83.2}
                    ]}
                ]',
                'lax $' COLUMNS (
                    NESTED PATH 'lax $[*]' COLUMNS (
                        continent varchar PATH 'lax $.continent',
                        NESTED PATH 'lax $.countries[*]' COLUMNS (
                            country varchar PATH 'lax $.name',
                            population double PATH 'lax $.population'
                        )
                    )
                ));

continent

country

population

Asia

Japan

125.7

Asia

Thailand

71.6

Europe

France

67.4

Europe

Germany

83.2

Следующий запрос использует PLAN для указания соединения OUTER между родительским путём и дочерним путём:

SELECT
      *
FROM
      JSON_TABLE(
                '[]',
                'lax $' AS "root_path"
                COLUMNS(
                    a varchar(1) PATH 'lax "A"',
                    NESTED PATH 'lax $[*]' AS "nested_path"
                            COLUMNS (b varchar(1) PATH 'lax "B"'))
                PLAN ("root_path" OUTER "nested_path")
                );

a

b

A

null

Следующий запрос использует PLAN для указания соединения INNER между родительским путём и дочерним путём:

SELECT
      *
FROM
      JSON_TABLE(
                '[]',
                'lax $' AS "root_path"
                COLUMNS(
                    a varchar(1) PATH 'lax "A"',
                    NESTED PATH 'lax $[*]' AS "nested_path"
                            COLUMNS (b varchar(1) PATH 'lax "B"'))
                PLAN ("root_path" INNER "nested_path")
                );

a

b

null

null

json_array#

Функция json_array создаёт массив JSON, содержащий заданные элементы.

JSON_ARRAY(
    [ array_element [, ...]
      [ { NULL ON NULL | ABSENT ON NULL } ] ],
    [ RETURNING type [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ] ]
    )

Типы аргументов#

Элементы массива могут быть произвольными выражениями. Каждое переданное значение преобразуется в элемент JSON в соответствии с его типом и необязательными спецификациями FORMAT и ENCODING.

Можно передавать значения SQL типов boolean, числовые и символьные строки. Они преобразуются в соответствующие литералы JSON:

SELECT json_array(true, 12e-1, 'text')
--> '[true,1.2,"text"]'

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

SELECT json_array(
                  '[  "text"  ] ' FORMAT JSON,
                  X'5B0035005D00' FORMAT JSON ENCODING UTF16
                 )
--> '[["text"],[5]]'

Можно также вкладывать другие функции, возвращающие JSON. В этом случае параметр FORMAT подразумевается неявно:

SELECT json_array(
                  json_query('{"key" : [  "value"  ]}', 'lax $.key')
                 )
--> '[["value"]]'

Другие переданные значения приводятся к varchar и становятся текстовыми литералами JSON:

SELECT json_array(
                  DATE '2001-01-31',
                  UUID '12151fd2-7586-11e9-8f9e-2a86e4085a59'
                 )
--> '["2001-01-31","12151fd2-7586-11e9-8f9e-2a86e4085a59"]'

Можно вообще опустить аргументы, чтобы получить пустой массив:

SELECT json_array() --> '[]'

Обработка null#

Если значение, переданное для элемента массива, равно null, оно обрабатывается в соответствии с указанной опцией обработки null. Если указано ABSENT ON NULL, элемент null опускается в результате. Если указано NULL ON NULL, JSON null добавляется в результат. ABSENT ON NULL — конфигурация по умолчанию:

SELECT json_array(true, null, 1)
--> '[true,1]'

SELECT json_array(true, null, 1 ABSENT ON NULL)
--> '[true,1]'

SELECT json_array(true, null, 1 NULL ON NULL)
--> '[true,null,1]'

Возвращаемый тип#

Стандарт SQL предписывает отсутствие выделенного типа данных для представления данных JSON в SQL. Вместо этого данные JSON представляются как символьные или двоичные строки. По умолчанию функция json_array возвращает varchar, содержащий текстовое представление массива JSON. С помощью условия RETURNING можно указать другой тип символьной строки:

SELECT json_array(true, 1 RETURNING VARCHAR(100))
--> '[true,1]'

Также можно указать использование varbinary и требуемую кодировку в качестве возвращаемого типа. Кодировка по умолчанию — UTF8:

SELECT json_array(true, 1 RETURNING VARBINARY)
--> X'5b 74 72 75 65 2c 31 5d'

SELECT json_array(true, 1 RETURNING VARBINARY FORMAT JSON ENCODING UTF8)
--> X'5b 74 72 75 65 2c 31 5d'

SELECT json_array(true, 1 RETURNING VARBINARY FORMAT JSON ENCODING UTF16)
--> X'5b 00 74 00 72 00 75 00 65 00 2c 00 31 00 5d 00'

SELECT json_array(true, 1 RETURNING VARBINARY FORMAT JSON ENCODING UTF32)
--> X'5b 00 00 00 74 00 00 00 72 00 00 00 75 00 00 00 65 00 00 00 2c 00 00 00 31 00 00 00 5d 00 00 00'

json_object#

Функция json_object создаёт объект JSON, содержащий заданные пары ключ-значение.

JSON_OBJECT(
    [ key_value [, ...]
      [ { NULL ON NULL | ABSENT ON NULL } ] ],
      [ { WITH UNIQUE [ KEYS ] | WITHOUT UNIQUE [ KEYS ] } ]
    [ RETURNING type [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ] ]
    )

Соглашения о передаче аргументов#

Существует два соглашения для передачи ключей и значений:

SELECT json_object('key1' : 1, 'key2' : true)
--> '{"key1":1,"key2":true}'

SELECT json_object(KEY 'key1' VALUE 1, KEY 'key2' VALUE true)
--> '{"key1":1,"key2":true}'

Во втором соглашении можно опустить ключевое слово KEY:

SELECT json_object('key1' VALUE 1, 'key2' VALUE true)
--> '{"key1":1,"key2":true}'

Типы аргументов#

Ключи могут быть произвольными выражениями. Они должны иметь тип символьной строки. Каждый ключ преобразуется в текстовый элемент JSON и становится ключом в создаваемом объекте JSON. Ключи не должны быть null.

Значения могут быть произвольными выражениями. Каждое переданное значение преобразуется в элемент JSON в соответствии с его типом и необязательными спецификациями FORMAT и ENCODING.

Можно передавать значения SQL типов boolean, числовые и символьные строки. Они преобразуются в соответствующие литералы JSON:

SELECT json_object('x' : true, 'y' : 12e-1, 'z' : 'text')
--> '{"x":true,"y":1.2,"z":"text"}'

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

SELECT json_object(
                   'x' : '[  "text"  ] ' FORMAT JSON,
                   'y' : X'5B0035005D00' FORMAT JSON ENCODING UTF16
                  )
--> '{"x":["text"],"y":[5]}'

Можно также вкладывать другие функции, возвращающие JSON. В этом случае параметр FORMAT подразумевается неявно:

SELECT json_object(
                   'x' : json_query('{"key" : [  "value"  ]}', 'lax $.key')
                  )
--> '{"x":["value"]}'

Другие переданные значения приводятся к varchar и становятся текстовыми литералами JSON:

SELECT json_object(
                   'x' : DATE '2001-01-31',
                   'y' : UUID '12151fd2-7586-11e9-8f9e-2a86e4085a59'
                  )
--> '{"x":"2001-01-31","y":"12151fd2-7586-11e9-8f9e-2a86e4085a59"}'

Можно вообще опустить аргументы, чтобы получить пустой объект:

SELECT json_object() --> '{}'

Обработка null#

Значения, передаваемые для ключей объекта JSON, не должны быть null. Допускается передавать null для значений объекта JSON. Значение null обрабатывается в соответствии с указанной опцией обработки null. Если указано NULL ON NULL, запись объекта JSON со значением null добавляется в результат. Если указано ABSENT ON NULL, запись опускается в результате. NULL ON NULL — конфигурация по умолчанию:

SELECT json_object('x' : null, 'y' : 1)
--> '{"x":null,"y":1}'

SELECT json_object('x' : null, 'y' : 1 NULL ON NULL)
--> '{"x":null,"y":1}'

SELECT json_object('x' : null, 'y' : 1 ABSENT ON NULL)
--> '{"y":1}'

Уникальность ключей#

Если обнаружен дублирующийся ключ, он обрабатывается в соответствии с указанным ограничением уникальности ключей.

Если указано WITH UNIQUE KEYS, дублирующийся ключ приводит к ошибке запроса:

SELECT json_object('x' : null, 'x' : 1 WITH UNIQUE KEYS)
--> failure: "duplicate key passed to JSON_OBJECT function"

Обратите внимание, что эта опция не поддерживается, если какой-либо из аргументов имеет спецификацию FORMAT.

Если указано WITHOUT UNIQUE KEYS, дублирующиеся ключи не поддерживаются из-за ограничений реализации. WITHOUT UNIQUE KEYS — конфигурация по умолчанию.

Возвращаемый тип#

Стандарт SQL предписывает отсутствие выделенного типа данных для представления данных JSON в SQL. Вместо этого данные JSON представляются как символьные или двоичные строки. По умолчанию функция json_object возвращает varchar, содержащий текстовое представление объекта JSON. С помощью условия RETURNING можно указать другой тип символьной строки:

SELECT json_object('x' : 1 RETURNING VARCHAR(100))
--> '{"x":1}'

Также можно указать использование varbinary и требуемую кодировку в качестве возвращаемого типа. Кодировка по умолчанию — UTF8:

SELECT json_object('x' : 1 RETURNING VARBINARY)
--> X'7b 22 78 22 3a 31 7d'

SELECT json_object('x' : 1 RETURNING VARBINARY FORMAT JSON ENCODING UTF8)
--> X'7b 22 78 22 3a 31 7d'

SELECT json_object('x' : 1 RETURNING VARBINARY FORMAT JSON ENCODING UTF16)
--> X'7b 00 22 00 78 00 22 00 3a 00 31 00 7d 00'

SELECT json_object('x' : 1 RETURNING VARBINARY FORMAT JSON ENCODING UTF32)
--> X'7b 00 00 00 22 00 00 00 78 00 00 00 22 00 00 00 3a 00 00 00 31 00 00 00 7d 00 00 00'

Предупреждение

Следующие функции и операторы не соответствуют стандарту SQL и считаются устаревшими. Согласно стандарту SQL, выделенного типа данных JSON не существует. Вместо этого значения JSON представлены как строковыми значениями. Остальная функциональность следующих функций покрывается функциями, описанными выше.

Приведение к JSON#

Следующие типы могут быть приведены к JSON:

  • BOOLEAN

  • TINYINT

  • SMALLINT

  • INTEGER

  • BIGINT

  • REAL

  • DOUBLE

  • VARCHAR

Дополнительно типы ARRAY, MAP и ROW могут быть приведены к JSON при выполнении следующих условий:

  • Типы ARRAY могут быть приведены, если тип элемента массива является одним из поддерживаемых типов.

  • Типы MAP могут быть приведены, если тип ключа MAP — VARCHAR, а тип значения MAP — поддерживаемый тип.

  • Типы ROW могут быть приведены, если тип каждого поля строки является поддерживаемым типом.

Примечание

Операции приведения с поддерживаемыми символьными строковыми типами обрабатывают входные данные как строку, без валидации как JSON. Это означает, что операция приведения со строковым входом, содержащим некорректный JSON, приведёт к успешному приведению в некорректный JSON.

Вместо этого рассмотрите использование функции json_parse() для создания валидированного JSON из строки.

Следующие примеры демонстрируют поведение приведения к JSON для этих типов:

SELECT CAST(NULL AS JSON);
-- NULL

SELECT CAST(1 AS JSON);
-- JSON '1'

SELECT CAST(9223372036854775807 AS JSON);
-- JSON '9223372036854775807'

SELECT CAST('abc' AS JSON);
-- JSON '"abc"'

SELECT CAST(true AS JSON);
-- JSON 'true'

SELECT CAST(1.234 AS JSON);
-- JSON '1.234'

SELECT CAST(ARRAY[1, 23, 456] AS JSON);
-- JSON '[1,23,456]'

SELECT CAST(ARRAY[1, NULL, 456] AS JSON);
-- JSON '[1,null,456]'

SELECT CAST(ARRAY[ARRAY[1, 23], ARRAY[456]] AS JSON);
-- JSON '[[1,23],[456]]'

SELECT CAST(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[1, 23, 456]) AS JSON);
-- JSON '{"k1":1,"k2":23,"k3":456}'

SELECT CAST(CAST(ROW(123, 'abc', true) AS
            ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)) AS JSON);
-- JSON '{"v1":123,"v2":"abc","v3":true}'

Приведение NULL к JSON не является тривиальным. Приведение отдельно стоящего NULL даст SQL NULL вместо JSON 'null'. Однако при приведении массивов или MAP, содержащих NULL, результирующий JSON будет содержать null.

Приведение из JSON#

Поддерживается приведение к типам BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE или VARCHAR. Приведение к ARRAY и MAP поддерживается, когда тип элемента массива является одним из поддерживаемых типов, или когда тип ключа MAP — VARCHAR, а тип значения MAP — один из поддерживаемых типов. Поведение приведения показано в следующих примерах:

SELECT CAST(JSON 'null' AS VARCHAR);
-- NULL

SELECT CAST(JSON '1' AS INTEGER);
-- 1

SELECT CAST(JSON '9223372036854775807' AS BIGINT);
-- 9223372036854775807

SELECT CAST(JSON '"abc"' AS VARCHAR);
-- abc

SELECT CAST(JSON 'true' AS BOOLEAN);
-- true

SELECT CAST(JSON '1.234' AS DOUBLE);
-- 1.234

SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER));
-- [1, 23, 456]

SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER));
-- [1, NULL, 456]

SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER)));
-- [[1, 23], [456]]

SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER));
-- {k1=1, k2=23, k3=456}

SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS
            ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN));
-- {v1=123, v2=abc, v3=true}

SELECT CAST(JSON '[123,"abc",true]' AS
            ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN));
-- {v1=123, v2=abc, v3=true}

Массивы JSON могут содержать элементы разных типов, а MAP JSON могут содержать значения разных типов. Это делает невозможным их приведение к массивам и MAP SQL в некоторых случаях. Для решения этой проблемы CedrusData поддерживает частичное приведение массивов и MAP:

SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON));
-- [JSON '[1,23]', JSON '456']

SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON));
-- {k1 = JSON '[1,23]', k2 = JSON '456'}

SELECT CAST(JSON '[null]' AS ARRAY(JSON));
-- [JSON 'null']

При приведении из JSON к ROW поддерживаются как массивы JSON, так и объекты JSON.

Другие функции JSON#

Помимо функций, подробно описанных в предыдущих разделах, доступны следующие функции:

is_json_scalar(json) → boolean#

Определяет, является ли json скалярным значением (т.е. числом JSON, строкой JSON, true, false или null):

SELECT is_json_scalar('1');         -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
json_array_contains(json, value) → boolean#

Определяет, существует ли value в json (строке, содержащей массив JSON):

SELECT json_array_contains('[1, 2, 3]', 2); -- true
json_array_get(json_array, index) → json#

Предупреждение

Семантика этой функции нарушена. Если извлечённый элемент является строкой, он будет преобразован в некорректное значение JSON, которое не заключено в кавычки должным образом (значение не будет окружено кавычками, а внутренние кавычки не будут экранированы).

Мы рекомендуем не использовать эту функцию. Она не может быть исправлена без влияния на существующее использование и может быть удалена в будущей версии.

Возвращает элемент по указанному индексу в json_array. Индекс начинается с нуля:

SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'

Эта функция также поддерживает отрицательные индексы для получения элементов, индексируемых с конца массива:

SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'

Если элемент по указанному индексу не существует, функция возвращает null:

SELECT json_array_get('[]', 0);                -- NULL
SELECT json_array_get('["a", "b", "c"]', 10);  -- NULL
SELECT json_array_get('["c", "b", "a"]', -10); -- NULL
json_array_length(json) → bigint#

Возвращает длину массива json (строки, содержащей массив JSON):

SELECT json_array_length('[1, 2, 3]'); -- 3
json_extract(json, json_path) → json#

Вычисляет JSONPath-подобное выражение json_path над json (строкой, содержащей JSON) и возвращает результат в виде строки JSON:

SELECT json_extract(json, '$.store.book');
SELECT json_extract(json, '$.store[book]');
SELECT json_extract(json, '$.store["book name"]');

Функция json_query предоставляет более мощную и функциональную альтернативу для разбора и извлечения данных JSON.

json_extract_scalar(json, json_path) → varchar#

Аналогична json_extract(), но возвращает значение результата в виде строки (в отличие от кодирования в JSON). Значение, на которое ссылается json_path, должно быть скалярным (boolean, число или строка).

SELECT json_extract_scalar('[1, 2, 3]', '$[2]');
SELECT json_extract_scalar(json, '$.store.book[0].author');
json_format(json) → varchar#

Возвращает текстовую сериализацию JSON из входного значения JSON. Это обратная функция к json_parse().

SELECT json_format(JSON '[1, 2, 3]'); -- '[1,2,3]'
SELECT json_format(JSON '"a"');       -- '"a"'

Примечание

json_format() и CAST(json AS VARCHAR) имеют совершенно различную семантику.

json_format() сериализует входное значение JSON в текст JSON, соответствующий RFC 7159. Значение JSON может быть объектом JSON, массивом JSON, строкой JSON, числом JSON, true, false или null.

SELECT json_format(JSON '{"a": 1, "b": 2}'); -- '{"a":1,"b":2}'
SELECT json_format(JSON '[1, 2, 3]');        -- '[1,2,3]'
SELECT json_format(JSON '"abc"');            -- '"abc"'
SELECT json_format(JSON '42');               -- '42'
SELECT json_format(JSON 'true');             -- 'true'
SELECT json_format(JSON 'null');             -- 'null'

CAST(json AS VARCHAR) приводит значение JSON к соответствующему значению SQL VARCHAR. Для строки JSON, числа JSON, true, false или null поведение приведения аналогично соответствующему типу SQL. Объект JSON и массив JSON не могут быть приведены к VARCHAR.

SELECT CAST(JSON '{"a": 1, "b": 2}' AS VARCHAR); -- ERROR!
SELECT CAST(JSON '[1, 2, 3]' AS VARCHAR);        -- ERROR!
SELECT CAST(JSON '"abc"' AS VARCHAR);            -- 'abc' (the double quote is gone)
SELECT CAST(JSON '42' AS VARCHAR);               -- '42'
SELECT CAST(JSON 'true' AS VARCHAR);             -- 'true'
SELECT CAST(JSON 'null' AS VARCHAR);             -- NULL
json_parse(string) → json#

Возвращает значение JSON, десериализованное из входного текста JSON. Это обратная функция к json_format():

SELECT json_parse('[1, 2, 3]');   -- JSON '[1,2,3]'
SELECT json_parse('"abc"');       -- JSON '"abc"'

Примечание

json_parse() и CAST(string AS JSON) имеют совершенно различную семантику.

json_parse() ожидает текст JSON, соответствующий RFC 7159, и возвращает значение JSON, десериализованное из текста JSON. Значение JSON может быть объектом JSON, массивом JSON, строкой JSON, числом JSON, true, false или null.

SELECT json_parse('not_json');         -- ERROR!
SELECT json_parse('["a": 1, "b": 2]'); -- JSON '["a": 1, "b": 2]'
SELECT json_parse('[1, 2, 3]');        -- JSON '[1,2,3]'
SELECT json_parse('"abc"');            -- JSON '"abc"'
SELECT json_parse('42');               -- JSON '42'
SELECT json_parse('true');             -- JSON 'true'
SELECT json_parse('null');             -- JSON 'null'

CAST(string AS JSON) принимает любое значение VARCHAR в качестве входных данных и возвращает строку JSON, значение которой установлено во входную строку.

SELECT CAST('not_json' AS JSON);         -- JSON '"not_json"'
SELECT CAST('["a": 1, "b": 2]' AS JSON); -- JSON '"[\"a\": 1, \"b\": 2]"'
SELECT CAST('[1, 2, 3]' AS JSON);        -- JSON '"[1, 2, 3]"'
SELECT CAST('"abc"' AS JSON);            -- JSON '"\"abc\""'
SELECT CAST('42' AS JSON);               -- JSON '"42"'
SELECT CAST('true' AS JSON);             -- JSON '"true"'
SELECT CAST('null' AS JSON);             -- JSON '"null"'
json_size(json, json_path) → bigint#

Аналогична json_extract(), но возвращает размер значения. Для объектов или массивов размер — это количество полей, а размер скалярного значения равен нулю.

SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x');   -- 2
SELECT json_size('{"x": [1, 2, 3]}', '$.x');          -- 3
SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x.a'); -- 0