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.

SELECT
  id,
  patient_identifier.value AS unique_member_id
FROM
  fhir_r4.patient AS p,
  p.identifier AS patient_identifier
WHERE
  patient_identifier."SYSTEM" = '<
http://www.plan.com/fhir/uniquememmberid
>'
LIMIT 10; 

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.

SELECT     DISTINCT patient_identifier."SYSTEM"
FROM fhir_r4.patient AS p, 
    p.identifier AS  patient_identifier
LIMIT 10;

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.

SELECT
  split_part(json_serialize(o.subject.reference),'/', 2) as patient,
  category_coding.display as coding_display,
  code_coding.code as loinc_code,
  code_coding.display as code_display,
  o.effectivedatetime,
  o.valuequantity.value,
  o.valuequantity.unit,
  o.valuestring,
  component_code_coding.code as component_code,
  component_code_coding.display as component_display,
  componento.valuequantity.value as component_value,
  componento.valuequantity.unit as component_unit
FROM 
  fhir_r4.observation o,
  o.category cat,
  cat.coding category_coding,
  o.code.coding code_coding,
  o.component componento,
  componento.code.coding component_code_coding
-- the select above would ignore records where o.component is null
-- due to this we need to add union below
UNION ALL
SELECT
  split_part(json_serialize(o.subject.reference),'/', 2) as patient,
  category_coding.display as coding_display,
  code_coding.code as loinc_code,
  code_coding.display as code_display,
  o.effectivedatetime,
  o.valuequantity.value,
  o.valuequantity.unit,
  o.valuestring,
  null as component_code,
  null as component_display,
  null as component_value,
  null as component_unit
FROM 
  fhir_r4.observation o,
  o.category cat,
  cat.coding category_coding,
  o.code.coding code_coding
WHERE 
  o.component is null
;

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.

SELECT DISTINCT count(*)
FROM fhir_r4.patient
WHERE birthdate < to_date('1951-12-26', 'yyyy-mm-dd');

Birth & Death Queries

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

SELECT DISTINCT
    p.*
FROM fhir_r4.patient p 
WHERE 
    p.birthdate > dateadd(MONTH, -6, getdate())
LIMIT 10;
SELECT
  p.*
FROM 
  fhir_r4.patient p
WHERE 
  p.deceasedboolean = true OR deceaseddatetime is not null
LIMIT 10;

Find Patient Observations

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

WITH observation AS 
(SELECT
  split_part(json_serialize(o.subject.reference), '/', 2) AS member_id,
  code_coding_unnested.code AS observation_code,
  code_coding_unnested.display AS observation_display
FROM
  fhir_r4.observation AS o, 
  o.code.coding AS code_coding_unnested
 )
 SELECT 
     member_id, 
     observation_code, 
     observation_display 
 FROM observation AS o
 INNER JOIN fhir_r4.patient ON o.member_id = fhir_r4.patient.id
LIMIT 10;

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.

WITH observation AS (
    SELECT 
      split_part(json_serialize(o.subject.reference), '/', 2) AS member_id,
      category_coding_unnested.version AS category_version,
      category_coding_unnested.code AS category_code,
      category_coding_unnested.display AS category_display,
      code_coding_unnested.code AS observation_code,
      code_coding_unnested.display AS observation_display
    FROM 
      fhir_r4.observation AS o,
      o.category AS category_unnested,
      o.code.coding AS code_coding_unnested,
      category_unnested.coding AS category_coding_unnested
)
SELECT o.*
FROM observation AS o
INNER JOIN fhir_r4.patient p ON o.member_id = p.id
WHERE 
  o.category_display = 'Vital Signs'
LIMIT 10;

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.

with observation as (
    SELECT
      distinct split_part(json_serialize(o.subject.reference), '/', 2) AS member_id,
      category_coding_unnested.display AS category_display,
      valueCodeableConcept_coding_unnested.code AS smoking_status_code,
      valueCodeableConcept_coding_unnested.display AS smoking_status_display
    FROM
      fhir_r4.observation o,
      o.category category_unnested,
      category_unnested.coding category_coding_unnested,
      o."valueCodeableConcept".coding valueCodeableConcept_coding_unnested 
 )
select o.* 
from observation as o
  INNER JOIN fhir_r4.patient p ON o.member_id = p.id
WHERE
  category_display = 'survey'
LIMIT 10;

​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.

SELECT
  *
FROM fhir_r4.coverage
WHERE 
  (
    json_serialize(period.start) > dateadd(month, -3, getdate())
    OR json_serialize(period."end") > dateadd(month, -3, getdate())
  )
LIMIT 10;

Find Duplicates in Coverage Data

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

SELECT 
  count(id) as n, 
  split_part(json_serialize(c.beneficiary.reference), '/', 2) as patient_id,
  period.start as period_start,
  period."end" as period_end
FROM 
  fhir_r4.coverage c
GROUP BY
2, 3, 4
HAVING 
  n > 1
ORDER BY 
  1