Mysql 5.7 ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 3

Question

iam using mysql 5.7.17 (latest version)

this is my table samp1

mysql> select *from samp1;  

+-----+----------------------------------------------------------------+
| id  | jdoc                                                           |
+-----+----------------------------------------------------------------+
| 101 | {"k1": 12, "k2": 34}                                           |
| 111 | {"k1": 12, "k2": 34, "k3": {"L1": "value1", "L2": "value2"}}   |
| 112 | {"k1": 12, "k2": 34, "k3": {"L1": "value1", "L2": "value2"}}   |
| 125 | {"7": "123", "8": 10, "9": "hey", "10": ["dar", "sne", "swo"]} |
+-----+----------------------------------------------------------------+

when i use below query it's executing properly

select id,jdoc -> "$.k3" as f from samp1  ;

but when i use this query with single number key. it throwing error,why?

select id,jdoc -> "$.7" as f from samp1  ;

 ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 3.

Have you tried `'$."7"'`? As per documentation, if a key is not valid in a path, you need to enclose it by double quotes. — Shadow, Jan 31 '17 at 13:19

score 25 · Accepted Answer · answered Jan 31 '17 at 13:27

25

This should work:

select id,jdoc -> '$."7"' as f from samp1

answered Jan 31 '17 at 13:27

Ike Walker

64,401
14
110
109

this also works for keys that have a dash `-` in them such as v4 uuids. – JCF Apr 09 '20 at 02:40

Mysql 5.7 ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 3

1 Answers1

Linked