I'm trying to select all columns where the roles
property in the json
column contains ANY of the values.
Statements I've tried:
SELECT * FROM components WHERE json->'$.roles' IN(1)
- this doesn't even work but it should in my opinion...
SELECT * FROM components WHERE JSON_CONTAINS(components, '1', '$.roles')
- this does work however is strict, so when I use
1
it pulls both like it should because they both contain 1, however if I insert1,2
orJSON_ARRAY(1,2)
it will only pull the later row because it isn't checking per array element...
I have the following table components
structure and data:
+====+========================================================================================================================================================================================================+==+
| id | json | |
+====+========================================================================================================================================================================================================+==+
| 1 | {"area": 1, "roles": [1], "elements": [{"home": {"content": "Home", "attributes": {"class": "my_class_1"}}}, {"dashboard": {"content": "Dashboard", "attributes": {"class": "my_class_1"}}}]} | |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| 2 | {"area": 1, "roles": [1, 2, 5], "elements": [{"home": {"content": "Testing", "attributes": {"class": "my_class_1"}}}, {"dashboard": {"content": "Dashboard", "attributes": {"class": "my_class_1"}}}]} | |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
Question: How can I modify either of these statements to allow them to query the rows based on the values in the roles
property?