Query through JSON Array with multiple values in MySQL

Question

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?

Dquote chars are string-typed value delimiters, not a part of a value. — Akina
– Akina, Commented Jul 1, 2021 at 12:51

Salman Arshad · Accepted Answer · 2021-07-01 12:54:39Z

4

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

edited Jul 1, 2021 at 12:54

answered Jul 1, 2021 at 12:49

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pavan Over a year ago

When I use this I get an error Error Code: 3141. Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 0.

Parth Kharecha · Accepted Answer · 2024-04-03 12:50:01Z

0

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

answered Apr 3, 2024 at 12:50

Parth Kharecha

6,5435 gold badges29 silver badges42 bronze badges

Comments

WeizhongTu · Accepted Answer · 2024-11-21 03:17:42Z

0

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

answered Nov 21, 2024 at 3:17

WeizhongTu

6,4344 gold badges39 silver badges52 bronze badges

Comments

mili · Accepted Answer · 2024-12-20 03:57:17Z

0

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.

answered Dec 20, 2024 at 3:57

mili

3,8522 gold badges32 silver badges30 bronze badges

Collectives™ on Stack Overflow

Query through JSON Array with multiple values in MySQL

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related