我有一个带有follow数组的json列:
[
{
"id": "24276e4b-de81-4c2c-84e7-eed9c3582a31",
"key": "id",
"type": "input",
},
{
"id": "e0ca5aa1-359f-4460-80ad-70445be49644",
"key": "name",
"type": "textarea",
}
]
我尝试了以下查询来获取文档列中具有id 24276e4b-de81-4c2c-84e7-eed9c3582a31的行,但它不返回结果:
select * from jobs WHERE document->'$[*].id' = "24276e4b-de81-4c2c-84e7-eed9c3582a31"
有谁知道如何做正确的查询?
最佳答案
我使用mysql 5.7,因此JSON_CONTAINS可以像这样轻松使用:
SELECT JSON_CONTAINS(
'[{"id": "24av","name": "she"},{"id": "e0c2", "name": "another_she"}]',
JSON_OBJECT('id', "e0c2")
);