[SOLVED] Query through JSON Array with multiple values in MySQL

Query through JSON Array with multiple values in MySQL

MySQL version: 5.7.29

I've a column layout_status with JSON datatype which contains array of values such as:

["Layouted"]
["Retired"]
["Verified"]
["Layouted", "Retired"]
["Layouted", "Verified"]

I want to select the records containing either "Layouted" or "Retired" or both.

I thought performing IN like SELECT * FROM users WHERE layout_status IN ('[\"Layouted\",\"Retired\"]'); would solve problem but it returns zero results.

Later I've tried with JSON_CONTAINS like below

SELECT * 
  FROM users
  WHERE ( JSON_CONTAINS(layout_status, "[\"Layouted\"]")
  OR      JSON_CONTAINS(layout_status, "[\"Retired\"]")
        )

But with the above query I get the records that has only either "Layouted" or "Retired". Records with ["Layouted", "Retired"] are ignored.

Is there any way to get all the records that contains "Layouted" or "Retired" or both?

Solution

You need to use JSON_CONTAINS properly. You want to search for a string inside an array, not an array inside an array:

WHERE JSON_CONTAINS(layout_status, '"Layouted"')
OR    JSON_CONTAINS(layout_status, '"Retired"')

Demo on db<>fiddle