Basic Single Timepoint Search

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.