Group by each array item

SQL++
1

Hi

I have a query case in n1ql, is there any way to group by and make aggregate on array items in entity.
for instance i have an entity like below :

{
   id : string,
   entityType : string,
   projectName : String,
   classes : [String]
}

some dummy records :

{ id:'1' , entityType : 'project' , projectName : 'x' , classes : ['alpha' , 'beta']}
{ id:'2' , entityType : 'project' , projectName : 'y' , classes : ['alpha']}
{ id:'3' , entityType : 'project' , projectName : 'z' , classes : [ 'beta']}
{ id:'1' , entityType : 'project' , projectName : '2' , classes : ['elena' , 'beta']}

and i want to have a result like below :

Select classes.field ,  count(*) from tableName
group by classes.field 

[
'alpha' : 2,
'beta' : 3,
'elena' : 1
]

is there any solution for case above ?

2

Hi @alidoustal
try:
Select classes.field , count(*) from tableName
UNNEST classes class
group by class;

For a live example, try following query with travel-sample:

select p, count(p) from travel-sample t UNNEST public_likes p where t.type = "hotel" group by p;

1 Like
3 
select   array_agg({d.c : d.ccount}) as classcount
from
(select c, count(*) ccount
from info unnest classes c
group by c) d

[
  {
    "classcount": [
      {
        "alpha": 2
      },
      {
        "beta": 3
      },
      {
        "elena": 1
      }
    ]
  }
]


OR

select raw {c : count(*)}
from info unnest classes c
group by c