Querying HealthLake data with SQL

The element extension is used to create custom fields in a data store.

This example shows you how to access the features of the extension element found in the Patient resource type.

When your HealthLake data store is imported into Athena, the elements of a resource type are parsed differently. Because the structure of the element is variable, it cannot be fully specified in the schema. To handle that variability, the elements inside the array are passed as strings.

In the table description of Patient, you can see the element extension described as array<string>, which means you can access the elements of array by using an index value. To access the elements of the string, however, you must use json_extract.

Here is a single entry from the extension element found in the patient table.

[{
		"valueString": "Kerry175 Cummerata161",
		"url": "http://hl7.org/fhir/StructureDefinition/patient-mothersMaidenName"
	},
	{
		"valueAddress": {
			"country": "DE",
			"city": "Hamburg",
			"state": "Hamburg"
		},
		"url": "http://hl7.org/fhir/StructureDefinition/patient-birthPlace"
	},
	{
		"valueDecimal": 0.0,
		"url": "http://synthetichealth.github.io/synthea/disability-adjusted-life-years"
	},
	{
		"valueDecimal": 5.0,
		"url": "http://synthetichealth.github.io/synthea/quality-adjusted-life-years"
	}
]

Even though this is valid JSON, Athena treats it as a string.

This SQL query example demonstrates how you can create a table that contains the patient-mothersMaidenName and patient-birthPlace elements. To access these elements, you need to use different array indices and json_extract.

SELECT
    extension[1],
    json_extract(extension[1], '$.valueString') AS MothersMaidenName,
    extension[2],
    json_extract(extension[2], '$.valueAddress.city') AS birthPlace
FROM patient

To learn more about queries that involve JSON, see Extracting data from JSON in the Amazon Athena User Guide.

Age is not an element of the Patient resource type in FHIR. Here are two examples for searches that filter based on age.

Because age is not an element, we use the birthDate for the SQL queries. To see how an element has been ingested into FHIR, search for the table name under Tables and views. You can see that it is of type string.

Example 1: Calculating a value for age

In this sample SQL query, we use a built-in SQL tool, current_date and year to extract those components. Then, we subtract them to return a patient's actual age as a column called age.

SELECT
	(year(current_date) - year(date(birthdate))) as age
FROM patient

Example 2: Filtering for patients who are born before 2019-01-01 and are male.

The SQL query shows you how to use the CAST function to cast the birthDate element as type DATE, and how to filter based on two criteria in the WHERE clause. Because the element is ingested as type string by default, we must CAST it as type DATE. Then you can use the < operator to compare it to a different date, 2019-01-01. By using AND, you can add a second criteria to the WHERE clause.

SELECT birthdate
FROM patient
-- we convert birthdate (varchar) to date  > cast that as date too
WHERE CAST(birthdate AS DATE) < CAST('2019-01-01' AS DATE) AND gender = 'male'

This example shows searches for locations within the Location resource type where the city name is Attleboro.

SELECT *
FROM Location
WHERE address.city='ATTLEBORO'
LIMIT 10;

SELECT birthdate
FROM patient
-- we convert birthdate (varchar) to date  > cast that as date too
WHERE CAST(birthdate AS DATE) < CAST('2019-01-01' AS DATE) AND gender = 'male'

The resource type condition stores diagnosis data related to issues that have risen to a level of concern. HealthLake's integrated medical natural language processing (NLP) generates new Condition resources based on details found in the DocumentReference resource type. When new resource are generated, HealthLake appends the tag SYSTEM_GENERATED to the meta element. This sample SQL query demonstrates how you can search the condition table and return results where the SYSTEM_GENERATED results have been removed.

To learn more about HealthLake's integrated natural language processing (NLP), see Integrated natural language processing (NLP) for HealthLake.

SELECT *
FROM condition
WHERE meta.tag[1] is NULL

You can also search within a specified string element to filter your query further. The modifierextension element contains details about which DocumentReference resource was used to generate a set of conditions. Again, you must use json_extract to access the nested JSON elements that are brought into Athena as a string.

This sample SQL query demonstrates how you can search for all the Condition that has been generated based off of a specific DocumentReference. Use CAST to set the JSON element as a string so that you can use LIKE to compare.

SELECT
    meta.tag[1].display as SystemGenerated,
    json_extract(modifierextension[4], '$.valueReference.reference') as DocumentReference
FROM condition
WHERE meta.tag[1].display = 'SYSTEM_GENERATED'

AND CAST(json_extract(modifierextension[4], '$.valueReference.reference') as VARCHAR) LIKE '%DocumentReference/67aa0278-8111-40d0-8adc-43055eb9d18d%'

The resource type, Observation stores measurements and simple assertions made about a patient, device, or other subject. HealthLake's integrated natural language processing (NLP) generates new Observation resources based on details found in a DocumentReference resource. This sample SQL query includes WHERE meta.tag[1] is NULL commented out, which means that the SYSTEM_GENERATED results are included.

SELECT valueCodeableConcept.coding[1].code
FROM Observation
WHERE  valueCodeableConcept.coding[1].code = '266919005'
-- WHERE meta.tag[1] is NULL

This column was imported as an struct. Therefore, you can access elements inside it using dot notation.

MedicationStatement is a FHIR resource type that you can use to store details about medications a patient has taken, is taking, or will take in the future. HealthLake's integrated medical natural language processing (NLP) generates new MedicationStatement resources based on documents found in the DocumentReference resource type. When new resources are generated, HealthLake appends the tag SYSTEM_GENERATED to the meta element. This sample SQL query demonstrates how to create a query that filters based off of a single patient by using their identifier and finds resources that have been added by HealthLake's integrated NLP.

SELECT *
FROM medicationstatement
WHERE meta.tag[1].display = 'SYSTEM_GENERATED' AND subject.reference = 'Patient/0679b7b7-937d-488a-b48d-6315b8e7003b';

To learn more about HealthLake's integrated natural language processing (NLP), see Integrated natural language processing (NLP) for HealthLake.