content_image/749708.jpg
## 12.18.6 JSON Table Functions 

<img src='content_image/749708.jpg'>

This section contains information about JSON functions that convert JSON data to tabular data. In MySQL 8.0.4 and later, one such function— JSON_TABLE() —is supported. 

• 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 Section 9.2, “Schema Object Names” ). 

Beginning with MySQL 8.0.27, this function compares column names in case-insensitive fashion. 

JSON_TABLE()  supports four types of columns, described in the following list: 

1. name  FOR ORDINALITY : This type enumerates rows in the COLUMNS  clause; the column named name  is a counter whose type is UNSIGNED INT , and whose initial value is 1. This is equivalent to specifying a column as AUTO_INCREMENT  in a CREATE TABLE  statement, and can be used to distinguish parent rows with the same value for multiple rows generated by a NESTED [PATH] clause. 

2. name type  PATH string_path  [ on_empty ] [ on_error ] : Columns of this type are used to extract values specified by string_path . type  is a MySQL scalar data type (that is, it cannot be an object or array). JSON_TABLE()  extracts data as JSON then coerces it to the column type, using 

