How to count json items in mysql

Question

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.

Using SELECT JSON_LENGTH(bouquet) FROM channels; i get 1 as result — John
– John, Commented Aug 30, 2017 at 17:18
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
– John, Commented Aug 30, 2017 at 17:22
There is another function JSON_DEPTH() .... see if that helps. — Just_Do_It
– Just_Do_It, Commented Aug 30, 2017 at 17:23

John · Accepted Answer · 2017-08-30 17:28:09Z

58

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)

answered Aug 30, 2017 at 17:28

John

1,7813 gold badges19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Virtual Gluck · Accepted Answer · 2025-03-24 13:18:05Z

1

JSON_LENGTH(sort) is wrong way

for example your table contains:

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

and SELECT JSON_LENGTH(sort) will return this result

+----+----------------+---------+
3
1
1
+----+----------------+---------+

answered Mar 24 at 13:18

Virtual Gluck

1031 silver badge5 bronze badges

Collectives™ on Stack Overflow

How to count json items in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related