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

[29 Dec 2020 17:32] Bill Karwin
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
    ->       mytable.json_column,
    ->       '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )
    ->     ) as jt;
| c1   |
| NULL |
1 row in set (0.00 sec)
[29 Dec 2020 18:29] MySQL Verification Team
Thank you for the bug report.
[25 Feb 16:19] Jon Stephens
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.