Best Practices

When you use 1up SQL on FHIR, we recommend that you use the following are best practices for writing your SQL queries to use SQL on FHIR. Whether you are familiar with SQL syntax, or new to writing SQL queries, this information is valuable because it describes the specific recommended methods of querying for 1up SQL on FHIR.

If you don’t follow these instructions, you might return incorrect results.

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.

SQL on FHIR is a columnar store. We recommend that you only query the fields that are required for efficient and optimized performance. For more information about columnar storage, see Columnar storage in the Amazon Redshift Database Developer Guide.

Reserved Words

Amazon Redshift has a list of reserved words. Some of the table fields that you use with SQL on FHIR might coincide with the reserved words. To successfully query the fields without encountering issues, make sure to include double quotes (“ “) around the field name.

The Patient table has a language field, but language is a reserved word. To query the language field successfully, use the following syntax.

select p."language"
from fhir_r4.patient p 
limit 10;

For a comprehensive list of all reserved words, see Reserved words in the Amazon Redshift Database Developer Guide.

Case Sensitivity

Some fields in FHIR tables include mixed-case values. To make sure you can successfully query the fields without encountering issues, you can enable the case-sensitive identifier.

To verify whether case sensitivity is enabled, run the following command.

SHOW enable_case_sensitive_identifier;

If the case sensitive identifier is not enabled, you can run the following command to enable it.

SET enable_case_sensitive_identifier to TRUE;

After you enable the case sensitive identifier, you can query mixed-case fields.

One example of a common mixed-case query is the valueCodeableConcept field in the fhir_r4.observation table.

select o."valueCodeableConcept"
from fhir_r4.observation o
limit 10;

Use SUPER

You can use the SUPER data type to store semistructured data as values. FHIR data is highly nested. You can use the SUPER data type to more easily store and interact with these complex data types.

In the fhir_r4.patient table, identifier is an example of a highly nested SUPER data type.

Screenshot of the SUPER data type example

In this example, to extract the system or the period start date, you can unnest this field to get access to that information. You can use JSON Functions with SQL on FHIR to parse the nested values and get their true data types.

For more information about how to use the SUPER type with Redshift, see SUPER type in the Amazon Redshift Database Developer Guide.

Unnest

A common task in SQL on FHIR is to query array data. SQL on FHIR supports the unnest function, which is used to expand an array into a relation that can be queried.

For example, you can query a FHIR table for Practitioner names. The name column in the fhir_r4.practitioner table is an array of structs, which contains a nest array for the given name. 

name: array | struct ->
  | id:varchar(256)
  | use:varchar(256)
  | family:varchar(256)
  | given: array | varchar(256)
  | prefix: array | varchar(256)
  | suffix: array | varchar(256)

When you write a query for this example, you must consider that fhir_r4.practitioner.name is an array of varying length that contains 0 or more elements. If you query only the first element from the array, you lose all other values that might be present. To get every element in the array, you must use the unnest function.

SELECT 
  id,
  unnested_name.family as "Last Name",
  given as "First Name"
FROM fhir_r4.practitioner as p,
  p."name" as unnested_name,
  unnested_name.given as given
LIMIT 10;

For more examples, see Example Queries.

For more information about unnesting, see Querying semistructured data in the AWS documentation.

Using JSON Functions

You can use the following JSON Functions to convert SQL data from one type to another.

  • JSON_SERIALIZE — Convert the SUPER data type to the VARCHAR data type

    For more information, see JSON_SERIALIZE function in the Amazon Redshift Database Developer Guide.

  • JSON_PARSE — Convert the VARCHAR data type to the SUPER data type

    For more information, see JSON_PARSE function in the Amazon Redshift Database Developer Guide.

Join Patient Resources

Joins are used to combine records from two or more related using a common identifier. You can use a join to query for a patient and all of that patient’s observations. SQL on FHIR uses local resource identifiers, in the RESOURCE_TYPE/ID form.

For example, an Observation resource contains a reference to a patient with the Patient/123xyz resource identifier. The Patient resource uses only the ID portion of that reference, which means that before you can use join you must first separate the local resource identifier resource type from the ID.

You can separate the local resource identifier with the split_part function in SQL on FHIR. In the Observation FHIR resource, the Patient identifier is found in the reference nested attribute of subject, which you can call using dot notation.

split_part(json_serialize(fhir_r4.observation.subject.reference), '/', 2) AS member_id

This function separates the string in fhir_r4.observation.subject.reference on the / slash character, and returns the second part, which is the ID.

Another function used in this example is json_serialize. The split_part function uses varchar as the first argument. Because fhir_f4.observation.subject.reference is a SUPER data type, you can use the json_serialize function to convert it to a VARCHAR data type.

SELECT
  split_part(json_serialize(o.subject.reference), '/', 2) AS member_id
FROM
  fhir_r4.observation o
  INNER JOIN fhir_r4.patient p ON split_part(json_serialize(o.subject.reference), '/', 2) = p.id
LIMIT 10;