I am wondering how I can loop through a JSON array to check for the same value exists 3 times.

First I am able to access the array like this:

select JSON_QUERY(json, '$[0].Data') as DATA
from tickets where id = 12

Result: [5,7,1,5,2,8,5,0,3]

I can drill down further to get specific values with:

select JSON_VALUE(json, '$[0].Data[0]') as DATA
from tickets where id = 12

Result: 5

How can I iterate through the Data array to check that the number 5 exists 3 times within the array? Thank you!

CodePudding user response：

You can try to use JSON_TABLE if your mysql version support that.

SELECT COUNT(*)
FROM tickets t1 
CROSS JOIN JSON_TABLE(
   t1.json,
   '$[0].Data' columns (num varchar(50) path '$')
) t2
WHERE num = '5'

sqlfiddle

CodePudding user response：

you can use mysql json_table function to convert your array into rows.

select t1.Value, count(1) from
       json_table(select JSON_QUERY(json, '$[0].Data') as DATA
                from tickets where id = 12
       ,"$[*]" columns(Value int path "$")) t1
group by t1.Value