Query a JSON column with an array of object in MySQL
I use mysql 5.7 and so JSON_CONTAINS can be easily used like this:
SELECT JSON_CONTAINS( '[{"id": "24av","name": "she"},{"id": "e0c2", "name": "another_she"}]', JSON_OBJECT('id', "e0c2") );
Try like this:
SELECT * FROM jobs WHERE document->'$[*].id' = json_array("24276e4b-de81-4c2c-84e7-eed9c3582a31");
It works for me, but I think the below way is more better:
SELECT * FROM jobs WHERE json_contains(document->'$[*].id', json_array("24276e4b-de81-4c2c-84e7-eed9c3582a31"));
Actually It's easy just remember the return value is JSON_TYPE
but not a String or something else;
maybe this? @Barmar
SELECT * FROM jobs WHERE JSON_SEARCH(document, "one", "24276e4b-de81-4c2c-84e7-eed9c3582a31", NULL, '$[*].id') IS NOT NULL;