Is it posible to use multiple values from a json list in a mysql where clause?-CodePudding

I have this table filled with values, and it's all structured in JSON.

PersonID	ValueID	Value
1	1	{"Values":[{"ID":1,"Value":true},{"ID":2,"Value":true}]}
1	2	{"Values":[{"ID":2,"Value":false},{"ID":3,"Value":true}]}

So I was wondering if there was any way to query on the ID and value at the same time, so I etc. would be able to search for "ID":1 and "Value":true and then it would return the first row.

I've tried to use JSON_CONTAINS_PATH, JSON_CONTAINS, JSON_SEARCH but none of them takes into account that I want to search in a list, I have tried with the $.Values[0].ID and that returns the id but I need to loop all of them through in the where, else I would only search the first index of the JSON array.

Can anyone point me in the right direction?

CodePudding user response：

SELECT
   PersonID,
   ValueID,
   x1.* 
FROM table1
cross join JSON_TABLE(table1.Value, 
   '$.Values[*]' COLUMNS( ID INTEGER PATH '$.ID',
                          Value INTEGER PATH '$.Value'
                        )) as x1

output:

PersonID	ValueID	ID	Value
1	1	1	1
1	1	2	1
1	2	2	0
1	2	3	1

see: DBFIDDLE

CodePudding user response：

SELECT *
FROM table1
WHERE table1.value->'$.Values[0]' = JSON_OBJECT('ID',1,'Value',true)