CODEWITHSUNDEEP
mysqljson
scorpio1441
scorpio1441

Reputation: 3088

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.

Upvotes: 1

Views: 763

Answers (1)

peterm
peterm

Reputation: 92785

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

Upvotes: 1

Related Questions