MySQL Bugs: #102089: JSON_TABLE() documentation has no example of a column reference

Bug #102089	JSON_TABLE() documentation has no example of a column reference
Submitted:	29 Dec 2020 17:32	Modified:	25 Feb 2021 16:19
Reporter:	Bill Karwin (Candidate Quality Contributor) (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The documentation page for JSON_TABLE() is https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html. It features several example SQL queries, but in all of them, the example shows only the usage of a JSON literal in a string as the first argument.

The documentation does briefly mention that the `expr` argument can be a column reference of another table, but no complete query example is shown.

Users are left with no example of using a column reference as the first argument, so they can apply JSON_TABLE() to data stored in another table.

There's a good example of a query in the MySQL Server Team blog that announced the JSON_TABLE() function in 2018 (https://mysqlserverteam.com/json_table-the-best-of-both-worlds/). But many users don't know to look here.

How to repeat:
This is a documentation omission, so there are no steps to repeat.

I came across this problem answering a question on Stack Overflow: https://stackoverflow.com/questions/65495811/mysql-8-subquery-using-json-table-not-working...

This is an example of how some users are confused about the correct syntax. 

Suggested fix:
Add to the documentation page a complete query that shows the usage of JSON_TABLE() with a reference to a column.

Suggestion for wording:

To apply JSON_TABLE() to data stored in a JSON column of a table, reference that column in the `expr` argument to JSON_TABLE(). Because table joins in the FROM clause are evaluated left-to-right, the table with the JSON column must be specified prior to the JSON_TABLE().

mysql> SELECT jt.*
    ->   FROM mytable
    ->     CROSS JOIN JSON_TABLE(
    ->       mytable.json_column,
    ->       '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )
    ->     ) as jt;
+------+
| c1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

Thank you for the bug report.

The cited documentation contains the following:

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')). 

Thus the information requested is already present in the documentation, and this is not a bug.