2

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Cogicero
  • 1,514
  • 2
  • 17
  • 36

1 Answers1

7

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Perfect! I was wondering why it was only a literal on the documentation. Many thanks!!! – Cogicero Dec 29 '20 at 18:09
  • P.S. Speaking of documentation please do you know if there is a way to do a select from a JSON_TABLE with a LIMIT and OFFSET? I have json data with lots of records, and I need to select within them. So I am first generating a temporary table (as above) and then selecting from there with a limit and offset. Which seems like quite a waste of resources to fetch all that data at first. I was wondering if you may know any (maybe undocumented) functionality for this? – Cogicero Dec 29 '20 at 18:11
  • 1
    No, you have to generate the temp table using JSON_TABLE() as you have done, then you can apply further relational operations to it, including selection conditions in the WHERE clause, projection expressions in the SELECT clause, and also GROUP BY, HAVING, ORDER BY, and LIMIT. Just as if it were another table. – Bill Karwin Dec 29 '20 at 18:16