mysqljson

How to count json items in mysql


I have table channels:

+----+----------------+---------+
| id | sort           | bouquet |
+----+----------------+---------+
|  1 | ["2","3","73"] | ["1"]   |
| 12 | ["3","73"]     | ["28"]  |
+----+----------------+---------+

And need to get count items in sort field for specific boquet...for example:

bouquet 1 have 3 sort items bouquet 12 have 2 sort items

I try using this query in mysql but i did not get idea how can i calculate number of items in sort field:

SELECT COUNT(sort) AS total_channels 
FROM channels 
WHERE JSON_SEARCH(bouquet, 'one', "1") IS NOT NULL;

I always get:

+----------------+
| total_channels |
+----------------+
|              1 |
+----------------+

Which is incorrect.


Solution

  • Using above help i come to this:

    SELECT JSON_LENGTH(sort) FROM channels WHERE bouquet='["1"]';
    

    And i get correct count:

    mysql> SELECT JSON_LENGTH(sort) FROM channels WHERE bouquet='["28"]';
    +-------------------+
    | JSON_LENGTH(sort) |
    +-------------------+
    |                 2 |
    +-------------------+
    1 row in set (0.00 sec)