CODEWITHSUNDEEP
mysqljson
Pavan
Pavan

Reputation: 33542

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?

Upvotes: 1

Views: 4484

Answers (4)

mili
mili

Reputation: 3812

You need to use JSON_OVERLAPS function, json_contains only search for a single value. but you want to select the records containing either "Layouted" or "Retired" or both.

WHERE JSON_OVERLAPS(layout_status, '["Layouted", "Retired"]')

Here second param for JSON_OVERLAPS is a json array, if you need search for another text, just add the text to this json array. You do not need to repeat JSON_OVERLAPS like in json_contains.

Upvotes: 0

WeizhongTu
WeizhongTu

Reputation: 6424

select json_contains('["Layouted", "Retired"]', '["Layouted"]'), json_contains('["Layouted", "Retired"]', '["Retired", "Layouted"]'), json_contains('["Layouted", "Retired"]', '["Retired2", "Layouted"]')

Upvotes: 0

Parth Kharecha
Parth Kharecha

Reputation: 6503

Use JSON_CONTAINS already explained but if you want full query, then you can checkout this

SELECT * FROM `table_name` WHERE JSON_CONTAINS(column_name,'"value1"') 
AND JSON_CONTAINS(column_name, '"value2"');

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272376

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

Upvotes: 4

Related Questions