Query a JSON column with an array of object in MySQL

Question

I have a json column with the follow array:

[
  {
    "id": "24276e4b-de81-4c2c-84e7-eed9c3582a31",
    "key": "id",
    "type": "input",
  },
  {
    "id": "e0ca5aa1-359f-4460-80ad-70445be49644",
    "key": "name",
    "type": "textarea",
    }
]

I tried the follow query to get the row that has the id 24276e4b-de81-4c2c-84e7-eed9c3582a31 in the document column, but it returns not results:

select * from jobs WHERE document->'$[*].id' = "24276e4b-de81-4c2c-84e7-eed9c3582a31"

Anyone know how to do the right query?

Answer 1

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")
                );