Alex G Alex G - 2 months ago 11
JSON Question

MySQL: search by JSON attribute returns error

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.

Answer

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"} |
+-----------------------------------------------+
Comments