Basic Single Timepoint Search
Learn how to create basic single timepoint searches in Atlas.
In this example, we will look for patients presenting with stroke in the ER. Let's first start by simply querying 1000 random patients for a single timepoint.
Note: While there are many formats to accomplish the same result in SQL, we suggest following our format due to the nested structure of medical records.
Query 1000 Random Patients
-- This is the table name which we copy paste into Atlas Enterprise
CREATE OR REPLACE TABLE `gradient-pacs-siskin-172863.eris_test.emergent_stroke` AS (
WITH
timepoint_1 AS (
-- We only select ids
SELECT
Patient.patient_id,
Study.row_id,
Series.series_uid
FROM `gradient-pacs-siskin-172863.eris_test.patients_json` AS Patient,
UNNEST(studies) AS Study,
UNNEST(Study.series) AS Series
-- This is where we will later add our filters
-- WHERE ...
)
-- This is where we format our query to be compatible with Atlas Enterprise
-- The schema is patient_id, row_ids[], series_uids[]
-- Where patient_id, row_ids and series_uids are just STRING and STRING[] datatypes
-- Each row represents a patient and all the study/series ids selected for that patient
SELECT
patient_id,
ARRAY_AGG(row_id) as row_ids,
ARRAY_AGG(series_uid) as series_uids
FROM timepoint_1
GROUP BY patient_id
ORDER BY patient_id
LIMIT 1000
)
Filtering for Stroke or Hemorrhage and CT Modality
Next, let's filter only for where stroke or hemorrhage are present in the positive findings of the radiology note. We also want to only look at CT modality.
CREATE OR REPLACE TABLE `gradient-pacs-siskin-172863.eris_test.emergent_stroke` AS (
WITH
timepoint_1 AS (
SELECT
Patient.patient_id,
Study.row_id,
Series.series_uid
FROM `gradient-pacs-siskin-172863.eris_test.patients_json` AS Patient,
UNNEST(studies) AS Study,
UNNEST(Study.series) AS Series
-- This is where we will later add our filters
WHERE
(Study.positive_entities LIKE '%stroke%' OR Study.positive_entities LIKE '%hemorrhage%')
AND Series.modality = 'CT'
)
SELECT
patient_id,
ARRAY_AGG(row_id) as row_ids,
ARRAY_AGG(series_uid) as series_uids
FROM timepoint_1
GROUP BY patient_id
ORDER BY patient_id
)
Additional Filtering for Manufacturer and Slice Thickness
Perhaps we only want a certain manufacturer with a max slice thickness.
CREATE OR REPLACE TABLE `gradient-pacs-siskin-172863.eris_test.emergent_stroke` AS (
WITH
timepoint_1 AS (
SELECT
Patient.patient_id,
Study.row_id,
Series.series_uid
FROM `gradient-pacs-siskin-172863.eris_test.patients_json` AS Patient,
UNNEST(studies) AS Study,
UNNEST(Study.series) AS Series
-- This is where we will later add our filters
WHERE
(Study.positive_entities LIKE '%stroke%' OR Study.positive_entities LIKE '%hemorrhage%')
AND Series.modality = 'CT'
AND REGEXP_CONTAINS(Series.manufacturer, "(?i)GE|G.E.|OEC Medical Systems")
AND Series.slice_thickness <= 1.25
)
SELECT
patient_id,
ARRAY_AGG(row_id) as row_ids,
ARRAY_AGG(series_uid) as series_uids
FROM timepoint_1
GROUP BY patient_id
ORDER BY patient_id
)
Note: Only the series matching the criteria are highlighted in blue in the UI. You can configure "smart select" settings to change selection behavior.