Group clauses

Figure 46. GroupByClause

GroupByClause

Grouping is also supported, like in SQL.

For each incoming tuple, the expression in the group clause is evaluated to an atomic (a grouping key). The incoming tuples are then grouped according to the key they are associated with.

For each group, a tuple is output, with a binding from the grouping variable to the key of the group.

Example 122. An order by clause.

for $x in collection("captains")
group by $century := $x.century
return { "century" : $century  }
      

Result (run with Zorba): { "century" : 21 } { "century" : 22 } { "century" : 23 } { "century" : 24 }


As for the other (non-grouping) variables, their values within one group are all concatenated, keeping the same name. Aggregations can be done on these variables.

The following query is equivalent to "SELECT century, COUNT(*) FROM captains GROUP BY century".

Example 123. An order by clause.

for $x in collection("captains")
group by $century := $x.century
return { "century" : $century, "count" : count($x) }
      

Result (run with Zorba): { "century" : 21, "count" : 1 } { "century" : 22, "count" : 1 } { "century" : 23, "count" : 1 } { "century" : 24, "count" : 4 }


JSONiq's group by is more flexible than SQL and is fully composable.

Example 124. An order by clause.

for $x in collection("captains")
group by $century := $x.century
return { "century" : $century, "captains" : [ $x.name ] }
      

Result (run with Zorba): { "century" : 21, "captains" : [ "Samantha Carter" ] } { "century" : 22, "captains" : [ "Jonathan Archer" ] } { "century" : 23, "captains" : [ "James T. Kirk" ] } { "century" : 24, "captains" : [ "Jean-Luc Picard", "Benjamin Sisko", "Kathryn Janeway" ] }


Unlike SQL, JSONiq does not need a having clause, because a where clause works perfectly after grouping as well.

The following query is the counterpart of "SELECT century, COUNT(*) FROM captains GROUP BY century HAVING COUNT(*) > 1"

Example 125. An order by clause.

for $x in collection("captains")
group by $century := $x.century
where count($x) gt 1
return { "century" : $century, "count" : count($x) }
      

Result (run with Zorba): { "century" : 24, "count" : 4 }