There is a mysql 5.7 data table in which one of the fields in the JSON format, for example, is:

{"string":"info","array":[1,2,3,4,5]}

To search for a string, in the column JSON which contains "info" the following structure is used:

 SELECT * FROM table WHERE json->"$.string" = 'info'

Question: how to select the rows of the table, in the json array of which there are certain values, for example, 1 and 3?

    1 answer 1

    Found:

     SELECT * FROM table WHERE json_contains(json->"$.array", json_array(1)) OR json_contains(json->"$.array", json_array(3))

    You can also search by type data:

     [ { id: 1, value: "value2", }, { id: 2, value: "value1", } ]

    =

     SELECT * FROM table WHERE json_contains(json->"$[*].id", json_array(1))
    • 1=1 AND can be safely removed - E_p
    • Well, this is for symmetry - Mic Crepe
    • Not for symmetry. So it is easier to generate the code for the request. And here is a living person for a living person. - E_p
    • Well, yes .. I delete - Mic Crepe