Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

+----+----------------+---------+
| 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.
                Yes i forget to put sort now it returns correct count values but can you please write here how to put WHERE bouquet="1"? bouqet is json type ["1"] so i can'not use bouquet="1"
– John
                Aug 30, 2017 at 17:22
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)
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.