小子在使用mysql的json时,键是数字,按常规方法访问:
SET @j = '{"0": {"a":1, "b":2}, "other": {"c": 3, "d": 4}}';
SELECT JSON_EXTRACT(@j, '$.0.a') AS '$.0.a'
但是报了一个异常:
Invalid JSON path expression. The error is around character position 3.
时间: 0s
查看官方文档,有如下说明:
Names of keys must be double-quoted strings or valid ECMAScript identifiers (see http://www.ecma-international.org/ecma-262/5.1/#sec-7.6). Path expressions, like JSON text, should be encoded using the ascii, utf8, or utf8mb4 character set. Other character encodings are implicitly coerced to utf8mb4.
文档地址:MySQL JSON 官方文档
解决方法:
数字键必须单独使用双引号包围,如下:
SET @j = '{"0": {"a":1, "b":2}, "other": {"c": 3, "d": 4}}';
SELECT JSON_EXTRACT(@j, '$."0".a') AS `$.0.a`;
+-------+
| $.0.a |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)