В этом разделе содержится информация о функциях JSON, которые преобразуют данные JSON в табличные данные. MySQL 8.4 поддерживает одну такую функцию, 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 Path.

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