MySQL JSON_EXTRACT path expression error

Question

The syntax looks right to me, any help would be appreciated!

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

fico7489 · Accepted Answer · 2020-11-17 12:27:00Z

65

Can you try this advice from https://dev.mysql.com/doc/refman/5.7/en/json.html

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

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

NOTE

If you have more fields, you must quote each key not the whole path:

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

edited Nov 17, 2020 at 12:27

fico7489

8,6507 gold badges59 silver badges93 bronze badges

answered Mar 1, 2016 at 23:20

alexander.polomodov

5,54414 gold badges44 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

2achary Over a year ago

Thank you! That was it. The quotes inside the quotes.

avelis Over a year ago

I really wish the Mysql documents would highlight that more. This answer did spare me from going down a rabbit hole.

Xapadoan Over a year ago

For compact syntax it seems like you should quote the entire path: fieldnames->"$.field.example-field-1" (with mysql8.0 at least)

IMB Over a year ago

Pure luck to have stumbled on this. Worth noting this issue also exist in MySQL 8 but not in MariaDB 10 (at least 10.4 for me, not tested on other versions)

Collectives™ on Stack Overflow

MySQL JSON_EXTRACT path expression error

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related