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

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

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

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

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

Язык пути JSON#

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

Синтаксис и семантика пути JSON#

Выражения пути JSON представляют собой рекурсивные структуры. Хотя название «путь» предлагает линейную последовательность операций, шаг за шагом углубляющуюся в JSON. структура, выражение пути JSON на самом деле является деревом. Он может получить доступ к вводу Элемент JSON несколько раз, разными способами и объедините результаты. Более того, результатом выражения пути JSON является не отдельный элемент, а упорядоченный последовательность предметов. Каждое из подвыражений принимает один или несколько входных данных. последовательности и возвращает последовательность в качестве результата.

Note

В слабом режиме большинство операций с путями сначала отменяют вложение всех массивов JSON в последовательность ввода. Любое отклонение от этого правила упоминается ниже. листинг. Режимы пути описаны в Режимы пути JSON.

Функции языка путей JSON делятся на: литералы, переменные, арифметические двоичные выражения, арифметические унарные выражения и группа операторы, известные под общим названием аксессоры.

литералы#

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

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

-1, 1.2e3, NaN

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

    Они заключены в двойные кавычки.

"Some text"

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

true, false

null

переменные#

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

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

$

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

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

$param

  • текущая переменная элемента

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

@

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

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

last

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

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

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

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

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

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

+ <path>
- <path>

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

метод доступа к члену#

Метод доступа к элементу возвращает значение элемента с указанным ключом для каждый объект 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.

Note

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

метод доступа к члену с подстановочным знаком#

Возвращает значения из всех пар ключ-значение для каждого объекта 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 появляются во входной последовательности.

метод доступа к члену-потомку#

Возвращает значения, связанные с указанным ключом во всех объектах 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

метод доступа к массиву#

Возвращает элементы по указанным индексам для каждого массива 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

метод доступа к массиву с подстановочными знаками#

Возвращает все элементы каждого массива 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 синтаксически аналогичны логическим выражениям в SQL. Однако семантика различается во многих аспектах:

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

  • У них есть своя обработка ошибок (никогда не подводят).

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

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

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

  • Соединение

<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 в контексте сравнения ведут себя иначе, чем NULL SQL:

  • ноль == ноль –>true

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

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

  • ноль по сравнению с массивом 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"}

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

двойной()#

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

<path>.double()

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

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

потолок(), пол() и абс()#

Получает верхний предел, нижний предел или абсолютное значение для каждого числового элемента в последовательность. Семантика операций такая же, как и в 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

ключевое значение()#

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

<path>.keyvalue()

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

  • «имя», которое является оригинальным ключом,

  • «значение», которое является исходным связанным значением,

  • «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. объекты. Однако внутри объектов порядок возвращаемых записей произволен.

тип()#

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

<path>.type()

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

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

  • "null"для нулевого значения JSON,

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

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

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

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

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

  • "time with time zone"для элемента типа время с часовым поясом,

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

  • "timestamp with time zone"для элемента типа timestamp с часовым поясом,

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

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

размер()#

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

<path>.size()

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

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

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

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

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

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

Режимы пути JSON#

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

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

Состояние

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

слабый режим

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

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

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

ОШИБКА

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

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

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

ОШИБКА

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

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

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

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

ОШИБКА

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

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

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

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

В следующем примере показан метод доступа к массиву с подстановочными знаками в нестрогом режиме. Массив 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 спецификация пути.

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, например. деление на ноль

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

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

'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 чувствителен к регистру, а язык 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.

идентификатор

описание

101

‘{“комментарий”: “мило”, “дети”: [10, 13, 16]}’

102

‘{“комментарий”: “проблемный”, “дети”: [8, 11]}’

103

‘{“комментарий”: “знает лучше всех”, “дети”: [2]}’

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

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

идентификатор

Children_above_ten

101

правда

102

правда

103

ложный

В следующем запросе режим пути является строгим. Проверяем третьего ребенка на каждый клиент. Это должно вызвать структурную ошибку для клиентов, которые делают не иметь троих и более детей. Эта ошибка обрабатывается в соответствии с параметром `ON Предложение ОШИБКА.

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

идентификатор

child_3_above_ten

101

правда

102

НУЛЬ

103

НУЛЬ

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.

'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 чувствителен к регистру, а язык 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это опция по умолчанию. С CONDITIONAL ARRAY WRAPPER оборачивает каждый результат, который не является одноэлементным JSON. массив или объект JSON.WITH UNCONDITIONAL ARRAY WRAPPERоборачивает каждый результат.

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

Примеры#

Позволятьcustomersбыть таблицей, содержащей два столбца:id:bigint, description:varchar.

идентификатор

описание

101

‘{“комментарий”: “мило”, “дети”: [10, 13, 16]}’

102

‘{“комментарий”: “проблемный”, “дети”: [8, 11]}’

103

‘{“комментарий”: “знает лучше всех”, “дети”: [2]}’

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

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

идентификатор

дети

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

идентификатор

дети

101

НУЛЬ

102

НУЛЬ

103

‘2’

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

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

идентификатор

последний_ребенок

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

идентификатор

дети

101

‘[13,16]’

102

‘[]’

103

‘[]’

Следующий запрос показывает результатQUOTESпункт. Обратите внимание, что ДЕРЖАТЬ 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

идентификатор

цитируемый_комментарий

нецитируемый_комментарий

101

‘’хороший’’

‘хороший’

102

‘’проблемный’’

‘проблемный’

103

«знает лучше»

«знает лучше»

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

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

  • Ошибки оценки пути JSON, например. деление на ноль

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

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.

'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 чувствителен к регистру, а язык 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, например. деление на ноль

  • Возвращенный скаляр, не конвертируемый в нужный тип.

Примеры#

Позволятьcustomersбыть таблицей, содержащей два столбца:id:bigint, description:varchar.

идентификатор

описание

101

‘{“комментарий”: “мило”, “дети”: [10, 13, 16]}’

102

‘{“комментарий”: “проблемный”, “дети”: [8, 11]}’

103

‘{“комментарий”: “знает лучше всех”, “дети”: [2]}’

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

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

идентификатор

комментарий

101

‘хороший’

102

‘проблемный’

103

‘знает лучше’

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

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

идентификатор

ребенок

101

10

102

8

103

2

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

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

идентификатор

ребенок

101

‘16’

102

‘ошибиться’

103

‘ошибиться’

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

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

идентификатор

ребенок

101

‘16’

102

‘пропал без вести’

103

‘пропал без вести’

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.

'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функция возвращает набор результатов, который вы можете использовать как любой другой 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"'
                  )
                )
              );

идентификатор

ребенок

wiki_data_id

1

Африка

1 квартал

2

Америка

Q828

3

Азия

Q48

4

Европа

В51

Следующий запрос использует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'
                        )
                    )
                ));

континент

страна

население

Азия

Япония

125,7

Азия

Таиланд

71,6

Европа

Франция

67,4

Европа

Германия

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")
                );

а

б

А

ноль

Следующий запрос использует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")
                );

а

б

ноль

ноль

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 логических, числовых и символьных строк. Они преобразуются в соответствующие литералы 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, это рассматривается в соответствии с указанный вариант обработки нуля. Если ABSENT ON 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. Ключи не должны быть нулевыми.

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

Вы можете передавать значения SQL логических, числовых и символьных строк. Они преобразуются в соответствующие литералы 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() --> '{}'

Обработка нуля#

Значения, передаваемые для ключей объекта JSON, не должны быть нулевыми. Разрешено пройти nullдля значений объекта JSON. Нулевое значение обрабатывается в соответствии с указанный вариант нулевой обработки. Если 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'

Warning

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

Преобразование в JSON#

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

  • BOOLEAN

  • TINYINT

  • SMALLINT

  • INTEGER

  • BIGINT

  • REAL

  • DOUBLE

  • VARCHAR

Кроме того,ARRAY, MAP, иROWтипы могут быть преобразованы в JSON, когда выполняются следующие требования:

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

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

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

Note

Операции приведения с поддерживаемыми типами символьных строк обрабатывает входные данные как строку, а не как 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будет производить SQLNULLвместо JSON 'null'. Однако при приведении из массивов или карты, содержащей NULLs, произведенныйJSONбудет иметьnullв этом.

Трансляция из JSON#

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

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 могут иметь смешанные типы элементов, а карты JSON могут иметь смешанные типы. типы значений. Это делает невозможным их приведение к массивам и картам SQL в некоторые случаи. Чтобы решить эту проблему, Trino поддерживает частичное приведение массивов и карт:

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#

Warning

Семантика этой функции нарушена. Если извлеченный элемент это строка, она будет преобразована в недействительную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#

Оценивает выражение типа JSONPathjson_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 functionобеспечивает более мощный и многофункциональная альтернатива анализу и извлечению данных JSON.

json_extract_scalar(json, json_path) → varchar#

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

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

Возвращает текст JSON, сериализованный из входного значения JSON. Это обратная функция к {func}.json_parse.

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

Note

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. Это обратная функция к {func}.json_format:

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

Note

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