MySQL: search by JSON attribute returns error

Question

I have a column in MySQL 5.7 table with data type json and following values:

{"1": "test"}
{"2": [25,23], "3": [28,54], "71": "test"}
{"2": [25,12], "3": [28,72], "33": "test2"}
{"2": [25,11], "3": [28,23], "63": "test3"}
{"4": "test4"}

I need to query all rows with attribute "3" (regardless of value), so I'm making this query:

SELECT * FROM `notes` WHERE JSON_CONTAINS(data, '{"2"}');

I'm getting:

#3141 - Invalid JSON text in argument 2 to function json_contains: "Missing a colon after a name of object member." at position 5.

Also tried:

SELECT data->"$.2" AS myattr FROM `notes`;
#3143 - Invalid JSON path expression. The error is around character position 3.

Please help.

peterm · Accepted Answer · 2016-09-08 03:18:08Z

1

Use JSON_CONTAINS_PATH since you don't care about the value

SELECT data 
  FROM notes 
 WHERE JSON_CONTAINS_PATH(data, 'one', '$."3"');

Sample output:

+-----------------------------------------------+
| data                                          |
+-----------------------------------------------+
| {"2": [25, 23], "3": [28, 54], "71": "test"}  |
| {"2": [25, 12], "3": [28, 72], "33": "test2"} |
| {"2": [25, 11], "3": [28, 23], "63": "test3"} |
+-----------------------------------------------+

answered Sep 8, 2016 at 3:18

peterm

93k16 gold badges157 silver badges162 bronze badges

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

Collectives™ on Stack Overflow

MySQL: search by JSON attribute returns error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related