Search code examples
mysqlsubquerymysql-json

MySQL 8: Subquery using JSON_TABLE not working unless subquery result is manually entered

Here is a minimum working edition of the problem I am facing. Following the JSON table example on the documentation page, I have the following table test in MySQL 8, where id is an INT field and data is a JSON field.

id     |   data
-----------------------------
1      |   [{"x": 2, "y": "8"}, {"x": "3", "y": "7"}, {"x": "4", "y": 6}]

I can successfully fetch the data using this statement (***):

SELECT data->"$[*]" as example FROM test t WHERE id = 1

which returns under the column "example":

[{"x": 2, "y": "8"}, {"x": "3", "y": "7"}, {"x": "4", "y": 6}]

Now I want to try to construct a JSON_TABLE from the returned data. If I manually enter the data into a JSON_TABLE query i.e.

SELECT * FROM JSON_TABLE(
    '[{"x": 2, "y": "8"}, {"x": "3", "y": "7"}, {"x": "4", "y": 6}]',
    "$[*]" COLUMNS (
        rowID FOR ORDINALITY,
        xval VARCHAR(100) PATH "$.x",
        yval VARCHAR(100) PATH "$.y"
    )
) AS tt

then it works fine per the documentation. I get the successful result:

rowID    xval    yval
  1        2       8
  2        3       7
  3        4       6

However when I use a select subquery, i.e.

SELECT * FROM JSON_TABLE(
    SELECT data->"$[*]" FROM test t WHERE id = 1,
    "$[*]" COLUMNS (
        rowID FOR ORDINALITY,
        xval VARCHAR(100) PATH "$.x",
        yval VARCHAR(100) PATH "$.y"
    )
) AS tt

then I get

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT data->"$[*]" FROM test t WHERE id = 1, "$[*]" COLUMNS ( rowID FOR OR' at line 2

I also tried wrapping the sub-select statement (***) into parenthesis i.e.

SELECT * FROM JSON_TABLE(
    (SELECT data->"$[*]" FROM test t WHERE id = 1),
    "$[*]" COLUMNS (
        rowID FOR ORDINALITY,
        xval VARCHAR(100) PATH "$.x",
        yval VARCHAR(100) PATH "$.y"
    )
) AS tt

but that gave the error

1210 - Incorrect arguments to JSON_TABLE.

I think this might have to do with quotes around the JSON object? I also tried JSON_QUOTE and JSON_UNQUOTE but no luck either. Please help. Thanks

Solution

  • This is the way to do it:

    SELECT tt.* FROM test
CROSS JOIN JSON_TABLE(
    test.data,
    '$[*]' COLUMNS (
        rowID FOR ORDINALITY,
        xval VARCHAR(100) PATH '$.x',
        yval VARCHAR(100) PATH '$.y'
    )
) AS tt
WHERE test.id = 1

    The documentation on JSON_TABLE() only shows using JSON literals, which is an unfortunate oversight.

    The original blog in which the MySQL Server team announced the JSON_TABLE() feature shows an example of using a column reference in place of the JSON literal.

    I have submitted a request for MySQL to add an example to the JSON_TABLE() documentation: https://bugs.mysql.com/bug.php?id=102089

    Update: the documentation request was closed as "not a bug" because they decided the documentation provided enough information without a code example.