I need to count true and false words in a JSON datatype. I have this JSON in the cell:
{"1": true, "2": false, "3": true}
The number of values may vary. I realize that I can count the total number of values in the array but how can I count true and false separately?
For total count I used JSON_LENGTH()
One option would be using below approach containing JSON_LENGTH(), JSON_EXTRACT() and JSON_SEARCH() functions together even for the version 5.7 (5.7.13+) where an array(js) extracted in the subquery and they're splitted to individual array for each values (true and false) by using JSON_SEARCH() function containing all as the second argument, and then counted by JSON_LENGTH() function :
SELECT ID,
JSON_LENGTH( JSON_SEARCH(js, 'all', 'true') ) AS Cnt_True,
JSON_LENGTH( JSON_SEARCH(js, 'all', 'false') ) AS Cnt_False
FROM ( SELECT *, JSON_EXTRACT(jsdata, '$.*') AS js
FROM tab ) t
provided JSON field has quoted values such as "true" and "false"
JSON_EXTRACT(jsdata, '$.*') still can be used in case that the boolean values are unquoted as in your case. But, this time some string operations would be needed. Here, I preferred using CHAR_LENGTH() function :
SELECT ID,
CHAR_LENGTH(js) - CHAR_LENGTH(REPLACE(js, 'true', SPACE(LENGTH('true')-1)))
AS Cnt_True,
CHAR_LENGTH(js) - CHAR_LENGTH(REPLACE(js, 'false', SPACE(LENGTH('false')-1)))
AS Cnt_False
FROM
( SELECT *, JSON_EXTRACT(jsdata, '$.*') AS js
FROM tab ) t
JSON_QUOTE but it doesn't works well...
Here is one option using json_table(), availabe in MySQL 8.0.
You can first turn each json object to an array of values using path $.*'. Then, you can pass the resulting json array to json_table(), which will put each value on a separate row. The final step is conditional aggregation.
Assuming that the json column is called js, that would be:
select sum(x.val = 'true') cnt_true, sum(x.val = 'false') cnt_false
from mytable t
cross join json_table(js -> '$.*', '$[*]' columns (val varchar(5) path '$')) x
Sample data (I added another row to make this more meaningful):
| js |
| :--------------------------------- |
| {"1": true, "2": false, "3": true} |
| {"bar": false, "foo": true} |
Results:
cnt_true | cnt_false
-------: | --------:
3 | 2
