content_image/844808.jpg content_image/844800.jpg content_image/844799.jpg
The JSON_TABLE()  function generates a table and provides another way to create a derived table: 

SELECT * FROM JSON_TABLE( arg_list ) [AS] tbl_name  ... 

The [AS] tbl_name  clause is mandatory because every table in a FROM  clause must have a name. Any columns in the derived table must have unique names. Alternatively, tbl_name  may be followed by a parenthesized list of names for the derived table columns: 

SELECT ... FROM ( subquery ) [AS] tbl_name  ( col_list ) ... 

The number of column names must be the same as the number of table columns. 

For the sake of illustration, assume that you have this table: 

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT); 

Here is how to use a subquery in the FROM  clause, using the example table: 

INSERT INTO t1 VALUES (1,’1’,1.0); INSERT INTO t1 VALUES (2,’2’,2.0); SELECT sb1,sb2,sb3 FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb WHERE sb1 > 1; 

Result: 

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

Here is another example: Suppose that you want to know the average of a set of sums for a grouped table. This does not work: 

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1; 

However, this query provides the desired information: 

SELECT AVG(sum_column1) FROM (SELECT SUM(column1) AS sum_column1 FROM t1 GROUP BY column1) AS t1; 

Notice that the column name used within the subquery ( sum_column1 ) is recognized in the outer query. 

The column names for a derived table come from its select list: 

mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt; 

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

To provide column names explicitly, follow the derived table name with a parenthesized list of column names: 

mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt (a, b, c, d); 

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

