How to group by JSON array ?

General Discussions - English
1

Hi,

I created an index with a json attribute, and I would like to use the “group by” feature on an array inside the JSON documents. But I don’t know how to group if the field in the json document is an array.

I got the following results :
mysql> select targets.country, count() from my_index group by targets.country;
±-----------------------------------------------------------------------------±---------+
| targets.country | count() |
±-----------------------------------------------------------------------------±---------+
| NULL | 7970 |
| [“united states of america”] | 76 |
| [“india”] | 12 |
| [“singapore”] | 2 |
| [“netherlands”] | 2 |
| [“korea”] | 6 |
| [“ethiopia”] | 1 |
| [“iran”,“india”,“georgia”,“united states of america”,“cuba”,“china”,“korea”] | 2 |
| [“iran”,“india”,“georgia”,“united states of america”,“cuba”,“china”,“korea”] | 2 |

As you can see, I would like to have a group for each country in the array, not a group by array. I think it is similar to the nested aggregation of Elasticsearch.
Do you have something similar in Manticore ?

2

Use groupby() instead:

mysql> create table my_index(f text, targets json);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into my_index(targets) values('{"country":["united states of america"]}');
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_index(targets) values('{"country":["iran", "india"]}');
Query OK, 1 row affected (0.00 sec)

mysql> insert into my_index(targets) values('{"country":["india", "united states of america"]}');
Query OK, 1 row affected (0.00 sec)

mysql> select groupby(), count(*) from my_index group by targets.country;
+--------------------------+----------+
| groupby()                | count(*) |
+--------------------------+----------+
| united states of america |        2 |
| iran                     |        1 |
| india                    |        2 |
+--------------------------+----------+
3 rows in set (0.00 sec)
3

Thank you Sergey ! It works perfectly.