Stream: cql
Topic: aggregation operators and grouping
Georg Fette (Nov 22 2019 at 11:53):
Hello I am comparing the properties of CQL and Neo4J's query language Cypher and have a question related to CQL's aggregation operators:
An aggregation operator has to be given a "scope" or a "grouping key" on which the operator performs the aggregation. In Cypher the scope is defined by all identifiers mentioned before the aggregation operator. Thus, "MATCH (A:Person) RETURN count(A.gender)" returns the amount of Person-instances with a given gender property, whereas "MATCH (A:Person) RETURN A, count(A.gender)" returns all Person-instances and in a second colunn the amount of gender properties that each Person-instance contains. Is such a aggregation grouping definition as well specified in CQL ? What should "[Person] A return Count(A.given)" and "[Person] A return A, Count(A.given)" return ?
Chris Moesel (Nov 22 2019 at 13:21):
The first expression [Person] A return Count(A.given) would return a List of Integers, with each item in the returned List corresponding to each person's number of reported given names.
I don't think the second expression is valid as-is. A return statement has to return a single item -- so you'd need to return it either as a Tuple or a List. Here is what it might look like as a Tuple:
[Person] A return { person: A, givenCount: Count(A.given) }
The above would return a List of Tuple { person Person, givenCount Integer } with each Tuple corresponding to an input Patient.
Chris Moesel (Nov 22 2019 at 13:24):
Note, however, that if we're talking about FHIR, then A.given isn't valid at all since given is actually on Patient.name (which is an array itself). So if you want a working FHIR example, those queries would have to change a bit.
Georg Fette (Nov 22 2019 at 22:35):
Oh, yeah, your right. I should have written "A.gender" instead of "A.given".
But why is "[Person] A return Count(A.given)" returning a list of numbers ? This assumes that the grouping is performed on each Person.
What does "[Person] A return Count(A)" return ? A list of ones ?
Is there a section in the documentation that states how to group within the aggregate operators ?
Georg Fette (Nov 22 2019 at 22:42):
ah, chapter 5.3. Query Evaluation gives me the explanation, okay.
Georg Fette (Nov 22 2019 at 23:03):
so for getting the amount of Persons the count operator has to be wrapped around the retrieve to do the trick ("Count([Person])") ?
Bryn Rhodes (Nov 22 2019 at 23:15):
Yes, that's correct, the aggregate operators in CQL take a list of values; CQL doesn't have a group by shorthand like SQL does, the aggregates are "primitive".
Last updated: Apr 12 2022 at 19:14 UTC
