sql - 在 mariadb json 中正确使用 json_table

Question

我想将名为 words 的数据库表中名为 data_table 的 json 列转换为表。栏目内容为:

{"123456":{"first":"hello","second":"there"},
 "78910":{"first":"All good?"}
}

我想达到这样的输出:

ID     | word     |
-------+----------|
123456 |hello     |
-------+----------|
78910  |All good? |

我试过这个:

SELECT * FROM data_table t1,
JSON_TABLE( t1.words, '$.123456.*' COLUMNS (word PATH '$.first')) AS jt;

但它返回 #1064 mysql 语法错误。错误在哪里？？？
完整的错误代码(从德语翻译):

Error in the SQL-Syntax. Please consult the manual for the correct syntax near '( t1.words, '$.123456.*' COLUMNS (word PATH '$.first')) AS jt LIMIT 0, 25' in line 2

我还注意到 this page 的基本示例:

SELECT *
FROM
JSON_TABLE(
    '[ {"c1": null} ]',
    '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )
) as jt;

还返回 #1064 语法错误(从德语翻译):

#1064 - There's an error in your SQL-Syntax. Please consult the manual for the correct syntax near '(
    '[ {"c1": null} ]',
    '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON E...' on line 3

怎么了？？？顺便说一句，我的主人说我的 mysql 版本是 10.3-MariaDB ..
更新
无论我在这里尝试什么，我总是收到 #1064 mysql 语法错误，告诉我在 {whatever come after JSON_TABLE( in my statement} 附近更正我的语法。怎么了？？
根据 this ，JSON_TABLE 实际上对我来说甚至不可用吗？？

Answer 1

MariaDB 10.6.0 中添加了 JSON_TABLE 功能。
为您的 SQL 报告的错误是由于缺少该值的类型。字段定义应该是:

COLUMNS (word TEXT PATH '$.first')

此外，如果您只想检查单个列，则应使用父对象而不是匹配路径表达式中的每个子对象:

SELECT * FROM data_table t1, JSON_TABLE(
  t1.words, '$.123456' COLUMNS (word TEXT PATH '$.first')
) AS jt;

+------------------------------------------------------------------------------+-------+
| words                                                                        | word  |
+------------------------------------------------------------------------------+-------+
| {"123456":{"first":"hello","second":"there"}, "78910":{"first":"All good?"}} | hello |
+------------------------------------------------------------------------------+-------+

如果要根据 WHERE 子句中的内容选择所有成员并进行过滤，可以使用以下形式:

SELECT * FROM data_table t1, JSON_TABLE(
  t1.words, '$.123456.*' COLUMNS (word TEXT PATH '$')
) AS jt;

+------------------------------------------------------------------------------+-------+
| words                                                                        | word  |
+------------------------------------------------------------------------------+-------+
| {"123456":{"first":"hello","second":"there"}, "78910":{"first":"All good?"}} | hello |
| {"123456":{"first":"hello","second":"there"}, "78910":{"first":"All good?"}} | there |
+------------------------------------------------------------------------------+-------+

$ 路径表达式指的是值本身，它对于将 JSON 输入作为 JSON 格式的输出传递很有用。
如果要首先提取具有该字段的所有对象的值，请使用 $.*.first 作为路径表达式:

SELECT * FROM data_table t1, JSON_TABLE(
  t1.words, '$.*.first' COLUMNS (word TEXT PATH '$')
) AS jt;

+------------------------------------------------------------------------------+-----------+
| words                                                                        | word      |
+------------------------------------------------------------------------------+-----------+
| {"123456":{"first":"hello","second":"there"}, "78910":{"first":"All good?"}} | hello     |
| {"123456":{"first":"hello","second":"there"}, "78910":{"first":"All good?"}} | All good? |
+------------------------------------------------------------------------------+-----------+