MySQL:按 JSON 属性搜索返回错误

Question

我在 MySQL 5.7 表中有一列数据类型为 json 和以下值:

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

我需要查询属性为“3”的所有行(无论值如何)，因此我进行了以下查询:

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

我得到:

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

还试过:

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

请帮忙。

Answer 1

使用JSON_CONTAINS_PATH因为你不关心值(value)

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

示例输出:

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