JSON_TABLE

JSON_TABLE — это функция-таблица, которая преобразует данные JSON в реляционную форму.

Синтаксис

JSON_TABLE(json_doc, 
          context_path COLUMNS (column_list)
) [AS] alias

column_list:
    column[, column][, ...]

column:
    name FOR ORDINALITY
    |  name type PATH path_str [on_empty] [on_error]
    |  name type EXISTS PATH path_str
    |  NESTED PATH path_str COLUMNS (column_list)

on_empty:
    {NULL | DEFAULT string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT string | ERROR} ON ERROR

Описание

JSON_TABLE можно использовать в контекстах, где может быть использована ссылка на таблицу; в операторе FROM в операторе SELECT, а также в операторах UPDATE/DELETE с несколькими таблицами.

json_doc — это документ JSON, из которого извлекаются данные. В самом простом случае это строковая константа, содержащая JSON. В более сложных случаях это может быть произвольное выражение, возвращающее JSON. Выражение может содержать ссылки на столбцы других таблиц. Однако можно ссылаться только на таблицы, которые предшествуют вызову JSON_TABLE. Для RIGHT JOIN предполагается, что его внешняя сторона предшествует внутренней. Все таблицы во внешних запросах также считаются предшествующими.

context_path — это выражение JSON Path, указывающее на набор узлов в json_doc, который будет использоваться в качестве источника строк.

Оператор COLUMNS объявляет имена и типы столбцов, которые возвращает JSON_TABLE, а также то, как генерируются значения столбцов.

Определения столбцов

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

Столбцы пути

name type PATH path_str [on_empty] [on_error]

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

set @json='
[
  {"name":"Laptop", "color":"black", "price":"1000"},
  {"name":"Jeans",  "color":"blue"}
]';

select * from json_table(@json, '$[*]' 
  columns(
   name  varchar(10) path '$.name', 
   color varchar(10) path '$.color',
   price decimal(8,2) path '$.price' ) 
) as jt;
+--------+-------+---------+
| name   | color | price   |
+--------+-------+---------+
| Laptop | black | 1000.00 |
| Jeans  | blue  |    NULL |
+--------+-------+---------+

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

Столбцы ORDINALITY

name FOR ORDINALITY

Подсчитывает строки, начиная с 1.

Пример:

set @json='
[
  {"name":"Laptop", "color":"black"},
  {"name":"Jeans",  "color":"blue"}
]';

select * from json_table(@json, '$[*]' 
  columns(
   id for ordinality, 
   name  varchar(10) path '$.name')
) as jt;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Laptop |
|    2 | Jeans  |
+------+--------+

Столбцы EXISTS PATH

name type EXISTS PATH path_str

Проверяет, существует ли узел, на который указывает value_path. value_path оценивается с использованием текущего узла источника строки в качестве контекстного узла.

set @json='
[
  {"name":"Laptop", "color":"black", "price":1000},
  {"name":"Jeans",  "color":"blue"}
]';

select * from json_table(@json, '$[*]' 
  columns(
   name  varchar(10) path '$.name',
   has_price integer exists path '$.price')
) as jt;
+--------+-----------+
| name   | has_price |
+--------+-----------+
| Laptop |         1 |
| Jeans  |         0 |
+--------+-----------+

ВЛОЖЕННЫЕ PATH

ВЛОЖЕННЫЙ PATH преобразует вложенные структуры JSON в несколько строк.

NESTED PATH path COLUMNS (column_list)

Находит последовательность узлов JSON, на которые указывает path, и использует её для создания строк. Для каждого найденного узла генерируется строка со значениями столбцов, указанными в операторе COLUMNS вложенного PATH. Если path не находит узлов, генерируется только одна строка со всеми столбцами, имеющими значения NULL.

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

set @json='
[
  {"name":"Jeans",  "sizes": [32, 34, 36]},
  {"name":"T-Shirt", "sizes":["Medium", "Large"]},
  {"name":"Cellphone"}
]';

ВЛОЖЕННЫЙ PATH позволяет генерировать отдельную строку для каждого размера каждого элемента:

select * from json_table(@json, '$[*]' 
  columns(
    name  varchar(10) path '$.name', 
    nested path '$.sizes[*]' columns (
      size varchar(32) path '$'
    )
  )
) as jt;
+-----------+--------+
| name      | size   |
+-----------+--------+
| Jeans     | 32     |
| Jeans     | 34     |
| Jeans     | 36     |
| T-Shirt   | Medium |
| T-Shirt   | Large  |
| Cellphone | NULL   |
+-----------+--------+

Операторы ВЛОЖЕННОГО PATH могут быть вложены друг в друга. Они также могут располагаться рядом друг с другом. В этом случае вложенные операторы PATH будут генерировать записи по одной за раз. Те, которые не генерируют записи, будут иметь все столбцы, установленные в NULL.

Пример:

set @json='
[
  {"name":"Jeans",  "sizes": [32, 34, 36], "colors":["black", "blue"]}
]';

select * from json_table(@json, '$[*]' 
  columns(
    name  varchar(10) path '$.name', 
    nested path '$.sizes[*]' columns (
      size varchar(32) path '$'
    ),
    nested path '$.colors[*]' columns (
      color varchar(32) path '$'
    )
  )
) as jt;

+-------+------+-------+
| name  | size | color |
+-------+------+-------+
| Jeans | 32   | NULL  |
| Jeans | 34   | NULL  |
| Jeans | 36   | NULL  |
| Jeans | NULL | black |
| Jeans | NULL | blue  |
+-------+------+-------+

Операторы ON EMPTY и ON ERROR

Оператор ON EMPTY задает, что делать, когда элемент, указанный путем поиска, отсутствует в документе JSON.

on_empty:
    {NULL | DEFAULT string | ERROR} ON EMPTY

Если оператор ON EMPTY отсутствует, подразумевается NULL ON EMPTY.

on_error:
    {NULL | DEFAULT string | ERROR} ON ERROR

Оператор ON ERROR задает, что делать, если при попытке извлечь значение, на которое указывает выражение пути, произойдет ошибка структуры JSON. Ошибка структуры JSON здесь возникает только при попытке преобразовать нескалярное значение JSON (массив или объект) в скалярное значение. Если оператор ON ERROR отсутствует, подразумевается NULL ON ERROR.

Примечание: Ошибка преобразования типов данных (например, попытка сохранить нецелое значение в поле целого типа или обрезка значения столбца varchar) не считается ошибкой JSON и поэтому не вызовет поведения ON ERROR. Она вызовет предупреждения, так же, как и CAST(value AS datatype).

Репликация

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

• сортировка членов объекта JSON по имени (как это делает MySQL)
• изменение способа обработки дублирующихся членов объекта, функция помечена как небезопасная для репликации на уровне инструкций.

Извлечение поддокумента в столбец

SELECT * FROM JSON_TABLE('{"foo": [1,2,3,4]}','$' columns( jscol json path '$.foo') ) AS T;
+-------+
| jscol |
+-------+
| NULL  |
+-------+

SELECT * FROM JSON_TABLE('{"foo": [1,2,3,4]}','$' columns( jscol json path '$.foo') ) AS T;
+-----------+
| jscol     |
+-----------+
| [1,2,3,4] |
+-----------+

См. также

• Поддержка JSON (видео)