Counting words in JSON array mySQL

Question

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

Answer 1

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