Group clauses

JSONiq follows the W3C standard for group by clauses. The following explanations, provided as an informal summary for convenience, are non-normative.

Figure 57. 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 127. 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 128. 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 129. 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 130. 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 }