Counting words in JSON array mySQL

user_13427298 0
Viewed 767

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()

count group-by mysql sql json
2 Answers

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

Demo

user_58413060

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

Demo on DB Fiddle

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
user_106767160
Related