MySQL JSON_EXTRACT 路径表达式错误

Question

语法对我来说是正确的，任何帮助将不胜感激!

mysql> select fieldnames from tablename limit 5;
+--------------------------------------------------------+
| fieldnames                                             |
+--------------------------------------------------------+
| {"example-field-1": "val2"}                            |
| {"example-field-2": "val1"}                            |
| {"example-field-1": "val1", "example-field-3": "val1"} |
| {"example-field-2": "val1"}                            |
| {"example-field-2": "val2"}                            |
+--------------------------------------------------------+
mysql> select JSON_EXTRACT(fieldnames, '$.example-field-1') from tablename;
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 17 in '$.example-field-1'.

MySQL 5.7.10

Answer 1

你能试试这个来自 https://dev.mysql.com/doc/refman/5.7/en/json.html 的建议吗？

As mentioned previously, path components that name keys must be quotedif the unquoted key name is not legal in path expressions. Let $ referto this value.

select JSON_EXTRACT(fieldnames, '$."example-field-1"') from tablename;

注意

如果你有更多的字段，你必须引用每个键而不是整个路径:

select JSON_EXTRACT(fieldnames, '$."field"."example-field-1"') from tablename;