Топ-100
 

JSON_TABLE

База данных: MySQL
Текст

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

Extracts data from a JSON document and returns it as a relational table
having the specified columns. The complete syntax for this function is
shown here:

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: This is an expression that returns JSON data. This can be a
constant ('{"a":1}'), a column (t1.json_data, given table t1 specified
prior to JSON_TABLE() in the FROM clause), or a function call
(JSON_EXTRACT(t1.json_data,'$.post.comments')).

path: A JSON path expression, which is applied to the data source. We
refer to the JSON value matching the path as the row source; this is
used to generate a row of relational data. The COLUMNS clause evaluates
the row source, finds specific JSON values within the row source, and
returns those JSON values as SQL values in individual columns of a row
of relational data.

The alias is required. The usual rules for table aliases apply (see
https://dev.mysql.com/doc/refman/8.0/en/identifiers.html).

URL: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

Пример