SQL Syntax: Where
HFQL provides two mechanisms for selecting specific data from the database: WHERE and HAVING. These mechanisms both work similarly to standard SQL but also have important differences.
The HFQL WHERE keyword is used much like the SQL keyword of the same name. It is used to choose and filter specific records (i.e. FHIR Resources) from the list of returned records.
Multiple WHERE clauses can be combined by using an AND keyword. Parentheses and OR expressions are not yet supported.
A WHERE clause can take several forms:
Using FHIR Search Parameter expressions allows you to use standard FHIR Search queries to filter records. This is generally far more performant than other ways of filtering data, so if it is possible to include at least one (if not all) of your filters in this way you will typically achieve significantly faster performance.
The format for this type of expression is:
WHERE id IN search_match('[param-name]', '[param-value]')
A complete example using this form follows:
SELECT
id, name[0].family, name[0].given
FROM
Patient
WHERE
id IN search_match('identifier', 'http://example.com|12345')
This query is internally transformed into the following FHIR search and executed directly against the underlying RDBMS.
http://base/Patient
Modifiers and other advanced search parameters are also supported. The following example shows a _has parameter being used to return the IDs of patients having an Observation generated by a device with the given identifier.
SELECT
id
FROM
Patient
WHERE
id IN search_match('_has:Observation:subject:device.identifier', 'http://foo|1234')
Values can include prefix comparators in HFQL expressions just as they can in search URLs. They can also be combined using the AND keyword.
SELECT
id
FROM
Observation
WHERE
id IN search_match('code', 'http://loinc.org|29463-7')
AND
id IN search_match('value-quantity', 'lt5|http://unitsofmeasure.org|kg')
HFQL statements can also use the WHERE keyword to apply FHIRPath Expressions to filter the returned records.
This is an extremely powerful way of filtering data since FHIRPath is a very expressive language, but it can also be very slow. In many cases the source data needs to be loaded individually from the RDBMS and filters applied after the data has been retrieved. If at all possible, using Search Parameters Expressions is preferable.
FHIRPath expressions have the advantage that they are not limited to existing SearchParameter indexes.
The following example shows a search for all cardiology note Observations (LOINC code 29463-7) where the string "running" appears anywhere in the text of the note.
SELECT id
FROM Observation
WHERE
id IN search_match('code', 'http://loinc.org|34752-6')
AND
value.ofType(string).lower().contains('running')
FHIRPath expressions can contain comparisons too. The following example shows a search for any weight assessment Observations (LOINC code 29463-7) with a value below 10 kg.
SELECT
id,
value.ofType(Quantity).value,
value.ofType(Quantity).system,
value.ofType(Quantity).code
FROM Observation
WHERE
code = 'http://loinc.org|29463-7'
AND
value.ofType(Quantity).value < 10