В этом разделе содержится информация о функциях JSON, которые преобразуют данные JSON в табличные данные. MySQL 9.2 поддерживает одну такую функцию, JSON_TABLE().

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

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

JSON_TABLE(
    expr,
    path COLUMNS (column_list)
)   [AS] alias

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

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

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

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

expr: Это выражение, которое возвращает данные JSON. Это может быть константа ('{"a":1}'), столбец (t1.json_data, таблица t1, указанная перед JSON_TABLE() в предложении FROM), или вызов функции (JSON_EXTRACT(t1.json_data,'$.post.comments')).

path: Выражение пути JSON, которое применяется к источнику данных. Мы будем ссылаться на значение JSON, соответствующее пути, как исходный ряд; это используется для генерации строки реляционных данных. Предложение COLUMNS оценивает исходный ряд, находит конкретные значения JSON в исходном ряду и возвращает эти значения JSON как значения SQL в отдельных столбцах строки реляционных данных.

alias обязательно. Обычно применяются правила для псевдонимов таблиц (см. Раздел 11.2, «Имена объектов схемы»).

Эта функция сравнивает имена столбцов без учета регистра.

JSON_TABLE() поддерживает четыре типа столбцов, описанные в следующем списке:

on empty, если указано, определяет, что делает JSON_TABLE() в случае отсутствия данных (в зависимости от типа). Это предложение также срабатывает в столбце в предложении NESTED PATH, когда у последнего нет совпадения и генерируется дополненная строка NULL. on empty принимает одно из следующих значений:

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

Указание ON ERROR до ON EMPTY является нестандартным и устаревшим в MySQL; попытка сделать это приводит к тому, что сервер выдает предупреждение. Ожидается, что поддержка нестандартного синтаксиса будет удалена в будущих версиях MySQL.

Когда значение, сохранённое в столбце, усечено, например, при сохранении 3.14159 в столбце типа DECIMAL(10,1), выдается предупреждение независимо от любого ON ERROR параметра. Когда несколько значений усекаются в одном операторе, предупреждение выдается только один раз.

Когда выражение и путь, переданные в эту функцию, приводят к значению JSON null, JSON_TABLE() возвращает SQL NULL в соответствии со стандартом SQL, как показано здесь:

mysql> SELECT *
    ->   FROM
    ->     JSON_TABLE(
    ->       '[ {"c1": null} ]',
    ->       '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )
    ->     ) as jt;
+------+
| c1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

Следующий запрос демонстрирует использование ON EMPTY и ON ERROR. Строка, соответствующая {"b":1}, пуста для пути "$.a", и попытка сохранить [1,2] как скаляр приводит к ошибке; эти строки выделены в выводе, показанном.

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
    ->     "$[*]"
    ->     COLUMNS(
    ->       rowid FOR ORDINALITY,
    ->       ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
    ->       aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
    ->       bx INT EXISTS PATH "$.b"
    ->     )
    ->   ) AS tt;

+-------+------+------------+------+
| rowid | ac   | aj         | bx   |
+-------+------+------------+------+
|     1 | 3    | "3"        |    0 |
|     2 | 2    | 2          |    0 |
|     3 | 111  | {"x": 333} |    1 |
|     4 | 0    | 0          |    0 |
|     5 | 999  | [1, 2]     |    0 |
+-------+------+------------+------+
5 rows in set (0.00 sec)

Имена столбцов подчиняются стандартным правилам и ограничениям, регулирующим имена столбцов таблиц. См. Раздел 11.2, «Имена объектов схемы».

Все выражения JSON и JSON-пути проверяются на валидность; невалидное выражение любого типа приводит к ошибке.

Каждое совпадение для path перед ключевым словом COLUMNS сопоставляется с отдельной строкой в таблице результатов. Например, следующий запрос даёт результат, показанный здесь:

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
    ->     "$[*]" COLUMNS(
    ->       xval VARCHAR(100) PATH "$.x",
    ->       yval VARCHAR(100) PATH "$.y"
    ->     )
    ->   ) AS  jt1;

+------+------+
| xval | yval |
+------+------+
| 2    | 8    |
| 3    | 7    |
| 4    | 6    |
+------+------+

Выражение "$[*]" соответствует каждому элементу массива. Вы можете фильтровать строки в результате, изменив путь. Например, использование "$[1]" ограничивает извлечение вторым элементом массива JSON, используемого в качестве источника, как показано здесь:

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
    ->     "$[1]" COLUMNS(
    ->       xval VARCHAR(100) PATH "$.x",
    ->       yval VARCHAR(100) PATH "$.y"
    ->     )
    ->   ) AS  jt1;

+------+------+
| xval | yval |
+------+------+
| 3    | 7    |
+------+------+

В определении столбца "$" передает всё совпадение в столбец; "$.x" и "$.y" передают только значения, соответствующие ключам x и y, соответственно, в этом совпадении. Для получения дополнительной информации см. Синтаксис JSON-путей.

NESTED PATH (или просто NESTED; PATH необязательно) создаёт набор записей для каждого совпадения в предложении COLUMNS, к которому оно относится. Если совпадений нет, все столбцы вложенного пути устанавливаются в значение NULL. Это реализует внешнее соединение между самым верхним предложением и NESTED [PATH]. Внутреннее соединение можно смоделировать, применив соответствующее условие в предложении WHERE, как показано здесь:

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',
    ->     '$[*]' COLUMNS(
    ->             a INT PATH '$.a',
    ->             NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
    ->            )
    ->    ) AS jt
    -> WHERE b IS NOT NULL;

+------+------+
| a    | b    |
+------+------+
|    1 |   11 |
|    1 |  111 |
|    2 |   22 |
|    2 |  222 |
+------+------+

Вложенные пути-близнецы — то есть два или более экземпляра NESTED [PATH] в одном предложении COLUMNS — обрабатываются один за другим, по одному за раз. Пока один вложенный путь создает записи, столбцы любых вложенных выражений пути-близнецов устанавливаются в значение NULL. Это означает, что общее количество записей для одного совпадения в одном содержащем предложении COLUMNS — это сумма, а не произведение всех записей, созданных модификаторами NESTED [PATH], как показано здесь:

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]',
    ->     '$[*]' COLUMNS(
    ->         a INT PATH '$.a',
    ->         NESTED PATH '$.b[*]' COLUMNS (b1 INT PATH '$'),
    ->         NESTED PATH '$.b[*]' COLUMNS (b2 INT PATH '$')
    ->     )
    -> ) AS jt;

+------+------+------+
| a    | b1   | b2   |
+------+------+------+
|    1 |   11 | NULL |
|    1 |  111 | NULL |
|    1 | NULL |   11 |
|    1 | NULL |  111 |
|    2 |   22 | NULL |
|    2 |  222 | NULL |
|    2 | NULL |   22 |
|    2 | NULL |  222 |
+------+------+------+

Столбец FOR ORDINALITY перечисляет записи, созданные предложением COLUMNS, и может использоваться для различения родительских записей вложенного пути, особенно если значения в родительских записях одинаковы, как показано здесь:

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a": "a_val",
    '>       "b": [{"c": "c_val", "l": [1,2]}]},
    '>     {"a": "a_val",
    '>       "b": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [22]}]}]',
    ->     '$[*]' COLUMNS(
    ->       top_ord FOR ORDINALITY,
    ->       apath VARCHAR(10) PATH '$.a',
    ->       NESTED PATH '$.b[*]' COLUMNS (
    ->         bpath VARCHAR(10) PATH '$.c',
    ->         ord FOR ORDINALITY,
    ->         NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')
    ->         )
    ->     )
    -> ) as jt;

+---------+---------+---------+------+-------+
| top_ord | apath   | bpath   | ord  | lpath |
+---------+---------+---------+------+-------+
|       1 |  a_val  |  c_val  |    1 | 1     |
|       1 |  a_val  |  c_val  |    1 | 2     |
|       2 |  a_val  |  c_val  |    1 | 11    |
|       2 |  a_val  |  c_val  |    2 | 22    |
+---------+---------+---------+------+-------+

Источник документа содержит массив из двух элементов; каждый из этих элементов создаёт две строки. Значения apath и bpath одинаковы во всём наборе результатов; это означает, что они не могут использоваться для определения того, пришли ли значения lpath из одних и тех же или разных родителей. Значение столбца ord остаётся неизменным в наборе записей с top_ord, равным 1, поэтому эти два значения — из одного объекта. Остальные два значения — из разных объектов, поскольку у них разные значения в столбце ord.

Обычно вы не можете присоединить производную таблицу, которая зависит от столбцов предшествующих таблиц в одном и том же предложении FROM. MySQL, в соответствии со стандартом SQL, делает исключение для табличных функций; они рассматриваются как производные таблицы lateral. Это подразумевается, и поэтому не разрешено перед JSON_TABLE(), также в соответствии со стандартом.

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

CREATE TABLE t1 (c1 INT, c2 CHAR(1), c3 JSON);

INSERT INTO t1 () VALUES
	ROW(1, 'z', JSON_OBJECT('a', 23, 'b', 27, 'c', 1)),
	ROW(1, 'y', JSON_OBJECT('a', 44, 'b', 22, 'c', 11)),
	ROW(2, 'x', JSON_OBJECT('b', 1, 'c', 15)),
	ROW(3, 'w', JSON_OBJECT('a', 5, 'b', 6, 'c', 7)),
	ROW(5, 'v', JSON_OBJECT('a', 123, 'c', 1111))
;

Затем вы можете выполнить соединения, например, такое, в котором JSON_TABLE() выступает в качестве производной таблицы, одновременно ссылаясь на столбец в ранее упомянутой таблице:

SELECT c1, c2, JSON_EXTRACT(c3, '$.*')
FROM t1 AS m
JOIN
JSON_TABLE(
  m.c3,
  '$.*'
  COLUMNS(
    at VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY,
    bt VARCHAR(10) PATH '$.b' DEFAULT '2' ON EMPTY,
    ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY
  )
) AS tt
ON m.c1 > tt.at;

Попытка использовать ключевое слово LATERAL в этом запросе вызывает .