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.
MySQL provides two operators ( -> and ->> ) to extract data from JSON columns. ->> will get the string value while -> will fetch value without quotes. As you can see ->> returns output as quoted strings, while -> returns values as they are. You can also use these operators in WHERE clause as shown below.
In MySQL, the JSON_LENGTH() function returns the length of a JSON document.
We can use the JSON_EXTRACT function to extract data from a JSON field. The basic syntax is: JSON_EXTRACT(json_doc, path) For a JSON array, the path is specified with $[index] , where the index starts from 0: mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]'); +------------------------------------------+
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With