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

Question

这是我面临的问题的最低工作版本。正在关注the JSON table example在文档页面上，我在 MySQL 8 中有下表 test，其中 id 是一个 INT 字段，data 是一个 JSON 字段。

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

我可以使用以下语句成功获取数据 (***):

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

在“example”列下返回:

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

现在我想尝试从返回的数据构造一个 JSON_TABLE。如果我手动将数据输入到 JSON_TABLE 查询中，即

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

然后根据文档它可以正常工作。我得到了成功的结果:

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

但是，当我使用选择子查询时，即

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

然后我得到

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

我还尝试将子选择语句 (***) 包装到括号中，即

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

但是这给出了错误

1210 - Incorrect arguments to JSON_TABLE.

我认为这可能与 JSON 对象周围的引号有关？我也试过 JSON_QUOTE 和 JSON_UNQUOTE 但也不走运。请帮忙。谢谢

Answer 1

方法是这样的:

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

documentation on JSON_TABLE()只显示使用 JSON 文字，这是一个不幸的疏忽。

原文blog in which the MySQL Server team announced the JSON_TABLE() feature显示了使用列引用代替 JSON 文字的示例。

我已提交请求，要求 MySQL 在 JSON_TABLE() 文档中添加示例:https://bugs.mysql.com/bug.php?id=102089

更新:文档请求被关闭为“不是错误”，因为他们认为文档在没有代码示例的情况下提供了足够的信息。