Ordered Search

Sometimes, you may want to order your data, for example to have only studies with positive finding hits return first. Or, perhaps you want to see patients with the most recent studies first. Let's show an example of this on the emergent stroke case. We provide the patients with the most number of studies that have a positive finding.

Here we provide the sorting value to be an indicator variable as to if our positive findings includes stroke or hemorrhage. You can have anything be a sorting value, you can also have multiple sorting value columns like (sorting_value_1, sorting_value_2, sorting_value_3). You can also decide whether these will be DESC or ASC.

Note: this feature requires the frontend to request the table using the sorting values. Currently, even if you save the table sorted, it will not return sorted.

CREATE OR REPLACE TABLE `gradient-pacs-siskin-172863.eris_test.emergent_stroke_ordered` AS (
  WITH 
    timepoint_1 AS (
        SELECT 
          Patient.patient_id, 
          Study.row_id, 
          Series.series_uid,
          CASE 
            WHEN Study.positive_entities LIKE '%stroke%' THEN 1 
            ELSE 0 
          END AS sorting_value_1,
          CASE 
            WHEN Study.positive_entities LIKE '%hemorrhage%' THEN 1 
            ELSE 0 
          END AS sorting_value_2,
        FROM `gradient-pacs-siskin-172863.eris_test.patients_json` AS Patient, 
        UNNEST(studies) AS Study,
        UNNEST(Study.series) AS Series
        WHERE 
          (Study.deid_english_report LIKE '%stroke%' OR Study.deid_english_report 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
    -- even though the table is saved sorted, frontend still needs to call it sorted.
    ORDER BY SUM(sorting_value_1), SUM(sorting_value_2), patient_id DESC
)

This query:

1. Creates sorting values based on whether stroke or hemorrhage is mentioned
2. Orders results with stroke mentions first, then hemorrhage mentions
3. Maintains consistent ordering with patient_id as the final sort key