Developers Introduction to CQL
This topic will serve as an introduction to software developers on the basics of CQL.
Atom is a simple text editor that includes a CQL highlighter. It is the recommended text editor for working with CQL, and can be dowloaded here: https://atom.io/ A CQL package can be installed to Atom by searching for language-cql in the install tab.
While Atom has been the recommended editor, on June 8 2022 github announced that Atom and all projects under Atom are to be archived with an official sunset of December 15 2022. The current CQL plugin for Atom lacks functionality that is present in the VS Code plugin.
It is recommended to transition to VS Code then install the Clinical Quality Language (CQL) plugin, currently version 0.2.0 as of September 2022, which has additional functionality all available here: https://code.visualstudio.com/
Documentation: https://code.visualstudio.com/docs
Clinical Quality Language (CQL) is a high-level query language. Its focus on what data is to be returned, rather than how it will be returned, allows the logic expressed by CQL queries to be used in a broad variety of implementation environments to achieve the same result. See the following link for examples on data retrieval in CQL: http://cql-runner.dataphoria.org
Expressions in CQL refer to any sequence that returns a value. Values can be simple (integers, strings, etc.), or they can be complex structures (or rows, also called tuples) like a QDM Encounter, Performed, which contains attributes such as admissionSource and relevantPeriod that are themselves values of different types.
In CQL, named expressions are defined by using the define statement:
define "Outpatient Encounters":
["Encounter, Performed": "Outpatient Encounter Codes"]
The identifier "Outpatient Encounters" can now be used as a named expression in other expressions.
Because CQL is a high-level query language, the query is a central construct to the language. Queries in CQL are clause-based. Each clause in a query is introduced with a different keyword, and must appear in a particular location within the query in order to be valid in CQL.
The general structure of a CQL query is:
<source> <alias>
<let clause>
<with or without clauses>
<where clause>
<return clause>
<sort clause>
All the clauses are optional, so the simplest query is just a source and an alias:
"Outpatient Encounters" Encounter
Here, the source is just a reference to Outpatient Encounters, which is an expression that returns a list of encounters. This source is given the alias Encounter. The alias allows the elements of the source to be referenced anywhere within the query. However, this simple query doesn't have any clauses, so it simply returns the same result as the source.
For more information about how queries are used in CQL, see the following link: https://cql.hl7.org/02-authorsguide.html#queries
The where keyword introduces a where clause, which allows you to filter the results of the source:
"Outpatient Encounters" Encounter
where Encounter.relevantPeriod during "Measurement Period"
This query returns only those encounters from the source whose relevantPeriod occurred entirely during the measurement period:
The where clause allows you to specify any condition in terms of the aliases introduced in the query, Encounter in this case. The condition in the where clause is evaluated for every encounter in the "Outpatient Encounters" query source, and the result then includes only those encounters for which the condition evaluated to true.
For more information on queries and specific clauses, see the following link: https://cql.hl7.org/02-authorsguide.html#queries
The with and without keywords are used to describe relationships between data.
The with keyword can be used to describe cases that should only be considered if a related data item is present:
["Encounter, Performed": "Outpatient"] Encounter
with ["Laboratory Test, Performed": "Streptococcus Test"] Test
such that Test.resultDatetime during Encounter.relevantPeriod
This query limits the outpatient encounters returned to only those that had a streptococcus test performed during the encounter. The such that clause describes the condition of the relationship, which is expressed in terms of the aliases Encounter (for the main source of the query) and Test, which is only available in the such that clause.
In addition, the without keyword can be used to describe cases that should only be considered if a particular data item is not present:
["Encounter, Performed": "Outpatient"] Encounter
without ["Laboratory Test, Performed": "Streptococcus Test"] Test
such that Test.resultDatetime during Encounter.relevantPeriod
This query limits the outpatient encounters returned to only those that did not have a streptococcus test performed during the encounter. Just like the with clause, the without clauses uses such that to describe the condition of the relationship.
For more information on queries and specific clauses, see the following link: https://cql.hl7.org/02-authorsguide.html#queries
While where and such that are similar, the difference is that such that is only valid as part of a with or without clause, and is the only place where the aliases introduced in those clauses can be referenced. For example, consider the following query:
["Encounter, Performed": "Outpatient"] Encounter
without ["Laboratory Test, Performed": "Streptococcus Test"] Test
such that Test.resultDatetime during Encounter.relevantPeriod
where Test.result is not null
This query is invalid because the alias Test is only available within the without clause that introduced it.
For more information on queries and specific clauses, see the following link: https://cql.hl7.org/02-authorsguide.html#queries
When combining with and without clauses, every additional clause will further restrict the cases that will be returned. This means that these clauses can't be used directly to express presence of one item or another. Combining with and without clauses optionally can be accomplished using a union operator:
"Encounters With Comfort Measures"
union "Encounters With Expected Discharge Status"
For more information on queries and specific clauses, see the following link: https://cql.hl7.org/02-authorsguide.html#queries
CQL makes use of a sort clause to order data returning from a query:
["Encounter, Performed": "Outpatient"] Encounter
sort by start of relevantPeriod
This query returns the results sorted by the start of the relevantPeriod element, ascending:
The alias Encounter is not used in the sort clause, as the sort applies only to the output of the query. There is no need to identify the source alias.
Also, any element of the result can be the target of the sort, but the values of the element must be comparable (i.e. must be able to be compared using <, > and =). Consider the following invalid query:
["Encounter, Performed": "Outpatient"] Encounter
sort by relevantPeriod
This query is invalid because relevantPeriod is an interval, and intervals cannot be unambiguously compared with >.
For more information on queries and specific clauses, see the following link: https://cql.hl7.org/02-authorsguide.html#queries
The let clause allows any number of definitions to be provided for a query. Each definition has access to all the available context of the query scope, as well as the overall library scope. This feature is extremely useful for simplifying query logic by allowing complex expressions to be defined and then reused within the context of a single query:
define "Consecutive Heart Rates Less Than 50 Inpatient":
from
["Physical Exam, Performed": "Heart rate"] HeartRate,
"Heart Failure Inpatient Encounter with Moderate or Severe LVSD" ModerateOrSevereLVSDHFInpatientEncounter
let PriorHeartRate: Last(["Physical Exam, Performed": "Heart rate"] MostRecentPriorHeartRate
where MostRecentPriorHeartRate.relevantPeriod during ModerateOrSevereLVSDHFInpatientEncounter.relevantPeriod
and MostRecentPriorHeartRate.relevantPeriod starts before start of HeartRate.relevantPeriod
sort by start of relevantPeriod
)
where HeartRate.relevantPeriod during ModerateOrSevereLVSDHFInpatientEncounter.relevantPeriod
and HeartRate.result < 50 '{Beats}/min'
and PriorHeartRate.result < 50 '{Beats}/min'
return HeartRate
For more information about the let clause, see the following link: https://cql.hl7.org/03-developersguide.html#introducing-context-in-queries
In SQL, joins are used to combine data from multiple tables. Although the focus in CQL is on the simplest possible expression of single-source queries, multi-source queries are possible through the use of joins:
define "Join Example":
from
["Encounter, Performed": "Outpatient"] Encounter,
["Laboratory Test, Performed": "Streptococcus Test"] Test
where Test.resultDatetime during Encounter.relevantPeriod
return { Encounter: Encounter, Test: Test }
For more information about how the from keyword is used in multi-source queries, see the following link: https://cql.hl7.org/03-developersguide.html#multi-source-queries
The following is a collection of join examples using various clauses:
A join using the with clause:
define "Semi-join Example":
["Encounter, Performed": "Outpatient"] Encounter
with ["Laboratory Test, Performed": "Streptococcus Test"] Test
such that Test.resultDatetime during Encounter.relevantPeriod
A join using the without clause:
define "Semi-minus Example":
["Encounter, Performed": "Outpatient"] Encounter
without ["Laboratory Test, Performed": "Streptococcus Test"] Test
such that Test.resultDatetime during Encounter.relevantPeriod
For more information about how to use the with and without clauses in joins, see the following link: https://cql.hl7.org/02-authorsguide.html#relationships
A join using the where clause and a return statement:
define "Join Example with Select":
from
["Encounter, Performed": "Outpatient"] Encounter,
["Laboratory Test, Performed": "Streptococcus Test"] Test
where Test.resultDatetime during Encounter.relevantPeriod
return {
encounterId: Encounter.id,
encounterCode: Encounter.code,
encounterRelevantPeriod: Encounter.relevantPeriod,
testId: Test.id,
testCode: Test.code,
testResultDatetime: Test.resultDatetime,
testResult: Test.result
}
A join using the let clause:
define "Left-outer-join Example":
["Encounter, Performed": "Outpatient"] Encounter
let Test: singleton from (["Laboratory Test, Performed": "Streptococcus Test"])
return { Encounter: Encounter, Test: Test }
A Cartesian product example:
define "Cartesian-product Example":
from
["Encounter, Performed": "Outpatient"] Encounter,
["Laboratory Test, Performed": "Streptococcus Test"] Test
The default result from a multi-source query is a tuple with an element for each query source. The following is an example of a possible multi-source query result:
{ Encounter: "Encounter, Performed", Test: "Laboratory Test, Performed" }
For more information about how lists are used in CQL, see the following link: https://github.com/cqframework/CQL-Formatting-and-Usage-Wiki/wiki/Authoring-Measures-in-CQL#lists
For more information about how Numbers, Strings, Rounding, Exponents, Quantities, Dates and Times are used in CQL, see the following link: https://github.com/cqframework/CQL-Formatting-and-Usage-Wiki/wiki/Authoring-Measures-in-CQL#values-and-calculation
For more information about how to calculate duration in Years, Months, Days, Hours and Minutes, see the following link: https://github.com/cqframework/CQL-Formatting-and-Usage-Wiki/wiki/Authoring-Measures-in-CQL#time-interval-calculations
For more information about Difference Calculations, see the following link: https://github.com/cqframework/CQL-Formatting-and-Usage-Wiki/wiki/Authoring-Measures-in-CQL#difference-calculations
For more information about intervals, see the following link: https://github.com/cqframework/CQL-Formatting-and-Usage-Wiki/wiki/Authoring-Measures-in-CQL#intervals
For the complete Developers Guide for CQL, see the following link: https://cql.hl7.org/03-developersguide.html
For a complete specification for CQL, see the following link: https://cql.hl7.org/index.html