Example Queries

You can use the following example SQL queries to gather common insights about your data. Each query demonstrates the techniques that 1up recommends you use when querying SQL on FHIR, such as unnesting, joining, and correctly pulling data.

These queries are written to pull data from common customer use cases. In some situations you might find that null values are returned or you might not see any results. The specific data available in your instance of SQL on FHIR varies depending on what data has been ingested into 1upHealth‘s FHIR Server.

If you think a certain piece of data should be available, but it isn’t returned in your results, contact your account manager.

For all of the example queries and code snippets in this topic, make sure to replace the placeholder text in the brackets (<code> or {query} with the correct parameters for your use case.

Unnesting Examples

A common method of to query array column data types is unnesting. This technique is the most efficient way of working with nested arrays when you’d like to expand the array elements. Though Redshift provides general guidance on using unnesting, the following examples demonstrate the technique for SQL on FHIR.

Find Distinct Patient Identifiers for an Identifier System

You can extract the correct member identifier from the FHIR identifier array by searching for the system string linked to it.

Find Distinct Patient Identifier Systems

You can extract the correct member identifier systems from the FHIR identifier array by searching for the system string linked to it.

Unnest Observation Components

FHIR Observation resources include the component field, which is complicated to query because it’s a varying length array, with 0 or more elements. To help you avoid missing data in the result set, you can query Observation resources using UNION.

Querying Patient Resources

You can use the examples in this section to learn how to query for common patient information and limit a query to a certain cohort of patients.

Count of Patients Born After a Date

You can use aggregation to count Patient resources for patients born after a certain date. You can use the to_date function to specify the date for date fields, such as birthdate.

Birth & Death Queries

You can use the date_add function to find resources relative to a date.

Find Patient Observations

This query joins a patient resource to their observations and returns the observation’s code and display text.

Find Vital Signs

Vital signs are stored in patient observations, and can be joined to the patient resource to return the patient’s member ID.

Find Smoking Status

Smoking status is one type of observation stored in a survey observation category. You can retrieve all surveys from observations and use the smoking_status_display column to get the smoking status.

​Query Coverage Resources

Coverage resources include data that can help you understand payments.

Find Coverage Data in a Date Range

Show members that have coverage started or ended within the last 3 months.

The word end is a reserved word and must be double-quoted.

Find Duplicates in Coverage Data

You can join coverage data to a patient to find any data that overlaps.