Before you start

• Check out our SQL tutorial

• Read our In-Time Metrics article where you’ll find useful resources to easily get time-based metrics from pre-built queries

Building your first query

You can also build your own queries from scratch. For that, it's important to undertand the underlying database schema.

To effectively run SQL queries on the Quantivly data layer, it's essential to familiarize yourself with the underlying database schema. The diagrams below provides a blueprint of how the data is structured, how tables are organized, and how they relate to each other.

Note that the diagrams are quite detailed, and the font may appear small. You can always zoom in to see the elements more clearly. In addition, to make the diagrams easier to understand, we've designed them with:

• Functional Organization: Tables are spatially arranged and annotated based on their functional roles, making it easier to locate the data relevant to your needs.

• Table Relationships: The schema illustrates relationships between tables, such as primary keys and foreign keys, which are crucial for joining tables and retrieving comprehensive data sets.

• Annotations and Descriptions: Each table and column includes annotations that provide additional context, such as data types, default values, and constraints.

TIPS: Understanding these schemas will help you construct your queries. We encourage you to refer to these diagrams as you work through the tutorial and develop your own queries.

The schema in public is the primary schema containing harmonized and structured concepts derived from both the Radiology Information System (RIS) and DICOM data. It contains both native concepts from the data sources and new concepts not native in the original data, but extrapolated from it. For example, this schema exposes information about:

• The imaging provider environment, including each facility with their own timezone, their imaging equipment, staff, etc.

• Appointments, harmonized and structured from RIS HL7 data, including scheduling orders, clinical orders and results

• Examinations and acquisitions, new concepts derived from DICOM studies and series that are essential from an operational perspective

• How appointments and examinations are linked, critical to evaluate metrics like delay, Slot utilization ratio, etc.

• Annotations, with a powerful system of recurring rules (e.g., able to annotate a "blocked time reserved for research every 3rd thursday of the month")

We have developed a powerful DICOM harmonization engine able to pivot the "Entity-Attribute-Value" (EAV) model of DICOM into a highly optimized, relational database. The result is in the dicom schema, specially designed to capture all the detailed, highly granular information in DICOM studies and series, including:

• DICOM study and series properties

• Series' modality-specific technical parameters

• Structured reports and their tree-like structure embedded into a relational database

Our schema is built around these core concepts:

• Naming conventions: Fields that closely match their DICOM counterparts have a dcm_ prefix, while fields without the dcm_ prefix represent new concepts added to our ontology. This naming convention helps differentiate original DICOM fields from the new concepts we've introduced, making the schema more intuitive to navigate.

• Concept of collection of slices: DICOM is fundamentally a slice-centric data model. For example, while many might refer to "the echo-time (TE) of a series" in MR, this is not possible and too limiting, e.g. as multi-echo sequences lead to multiple TE values within the same series. Similarly, many DICOM attributes can vary within a single series—we've even encountered different spatial resolutions in the same series! To address this, and "pivot" the EAV model of DICOM into a relational database, we've introduced the concept of collections of slices, such as 3D volumes for MRI (mr_volume) and CT (ct_volume), each associated with their acquisition parameters (e.g., TE, TR, resolution, b-value, etc.). Implementing this required decoding proprietary information from each vendor (e.g., SIEMENS MOSAIC, Philips Enhanced DICOM, GE DICOM, Siemens XA DICOM, etc.), but now you can simply query your data using these higher-level concepts!

• Concept of aliases: You probably know it: certain DICOM attributes tend to change names over time, e.g., the scanners' StationName, the InstitutionName, InstitutionAddress, etc. Even ManufacturerModelName or Manufacturer sometimes change during scanner upgrades. As a result, a same DICOM equipment (or institution) can have different DICOM identities. Our ontology includes a mechanism of aliases that allows us to group these various identities under a single, consistent identity (e.g., many dicom.equipment_alias for one dicom.equipment). You won't have to alias things yourself; we've developed rules and alerts to detect these changes early and handle the aliasing for you.

• Study aliases: While you may not need to know this in detail, it's still interesting to understand. Each DICOM series repeats the information of the study it relates to, but this information can be inconsistent across series (DICOM is beautiful 😉 ). That's why, for every DICOM series, we maintain the version of the study as described in that series—this is what the study_alias represents. Meanwhile, in the study table, you have access to the harmonized version across all study aliases, typically determined using a majority voting mechanism to establish the "best guess" value at the study level.

We'll cover the basic concepts and provide examples to help you understand how to retrieve the information you need.

The Quantivly harmonization engine extracts the concepts of examinations and acquisitions from DICOM studies and series. Specifically, an examination is defined as a contiguous collection of acquisitions for the same patient on the same equipment.

Start by examining the central table called examination. Here's a basic query to retrieve the examination_id (primary key)and the corresponding examination_datetime for the 100 most recent examinations:

select
    examination_id,
    examination_datetime
from examination
order by examination_datetime desc
limit 100;

\scriptsize
\begin{verbatim}
 examination_id |     examination_datetime
----------------+-------------------------------
        1600660 | 2024-10-28 05:57:29.895+00
        1600663 | 2024-10-28 05:04:50.473024+00
        1600543 | 2024-10-28 04:25:38.211721+00
        1600644 | 2024-10-28 03:01:39.8825+00
        1600657 | 2024-10-28 02:46:04.883812+00
        1600639 | 2024-10-28 01:25:50.905+00
        ...
\end{verbatim}
\normalsize

You can start to add more columns to inspect properties of those examinations:

select
    examination_id,
    examination_datetime,
    accession_numbers,
    study_descriptions
from examination
order by examination_datetime desc
limit 100;

 examination_id |     examination_datetime      | accession_numbers |          study_descriptions
----------------+-------------------------------+-------------------+---------------------------------------
        1600660 | 2024-10-28 05:57:29.895+00    | {101116421}       | {"MR SPINE ENTIRE W AND WO CONTRAST"}
        1600663 | 2024-10-28 05:04:50.473024+00 | {101116412}       | {"CT HEAD WO IV CONTRAST"}
        1600543 | 2024-10-28 04:25:38.211721+00 | {101116396}       | {"CT HEAD WO IV CONTRAST"}
        1600644 | 2024-10-28 03:01:39.8825+00   | {101116204}       | {"MR BRAIN W AND WO CONTRAST"}
        1600657 | 2024-10-28 02:46:04.883812+00 | {101116313}       | {"CT ABDOMEN PELVIS W CONTRAST"}
        1600639 | 2024-10-28 01:25:50.905+00    | {101116202}       | {"MR LUMBAR SPINE WO CONTRAST"}
        ...

Above you can see the accession numbers and study descriptions for each examination. Note that these are arrays, as you may have different accession numbers and/or study descriptions within an examination (e.g., think of a “MR Brain w/wo Contrast + MR Spine w/wo Contrast” combo exam in which acquisitions pre- and post- contrast are intertwined).

Often, you'll want to count the number of examinations. You can achieve this using the count() function. For example, the number of exams yesterday:

select
    count(distinct examination_id) as exam_count
from examination
where extract(year from examination_datetime) = 2024;

 exam_count
------------
       3450

Important Reminder: Be aware that joining tables may result in duplicate rows. For example, if you were to join examination with the acquisition table (more on that later), the number of rows would be multiplied.

To ensure accuracy, especially when counting entries, use distinct inside of a count function to obtain unique counts of examinations. This method helps maintain the integrity of your data analysis by avoiding inflated results due to duplicate records.

Adding distinct to the count function below changed the output of 8 (because there were 8 acquisitions for this examination, resulting is 8 rows returned) to the correct count, 1.

select
    count(distinct examination_id)
from examination
inner join acquisition using (examination_id)
where 27121919 = any(accession_numbers)

The primary way to identify information about imaging equipment is via the imaging_resource and resource tables in the main schema. For example, to list all available imaging resources and their name:

select 
    resource_id,
    name 
from resource 
inner join imaging_resource using (resource_id);

    resource_id |      name
    ------------+-----------------
            2   | B MR1
            8   | B MR2
            10  | B MR3
            3   | B MR4
            1   | B MR5
            6   | W MR3T
            4   | P MR3T
    (7 rows)

Note: while resource and imaging_resource have their own primary key (resource_id and imaging_resource_id, respectively), we have in the database a constraint enforcing resource_id = imaging_resource_id. Knowing that can be convenient to avoid one join in complex queries.

Imaging resources in our ontology corresponds to a high level, functional concept - you can think of it as an MR Room for an MR device, for example (but not the device itself). The specific DICOM equipment (see below) of an imaging_resource is linked via the resource_to_dicom_equipment associative table.

Here is an example query counting the number of examinations for each imaging resource:

select
    resource.name,
    count(distinct examination_id) as exam_count
from examination
inner join resource_to_dicom_equipment using (dicom_equipment_id)
inner join resource using (resource_id)
where extract(year from examination_datetime) = 2024
group by resource.name;

  name  | exam_count
--------+------------
 B MR1  |       2154
 B MR2  |       1997
 B MR3  |       2715
 B MR4  |       4123
 B MR5  |       3570
 P MR3T |       1758
 W MR3T |       3508
(7 rows)

Important: Although an imaging_resource is most often linked to a single dicom.equipment, it can be associated with multiple pieces of equipment. This occurs when the device of an imaging_resource is replaced—e.g., when an old 1.5T scanner is replaced with a newer 3T scanner in the same room. These two devices have different "DICOM identities" but can be associated with the same imaging_resource.

By focusing on the high-level, functional concept of an imaging resource, the imaging_resource allows you to accurately and continuously evaluate metrics (e.g., examination counts) over time, even when equipment changes. This data structure also enables you to compare metrics before and after equipment replacements if needed.

The dicom.equipment table contains the specific DICOM equipment identities, with their serial numbers, station names, and more. The query below shows DICOM characteristics of imaging resources:

select
    resource.name,
    equipment.manufacturer,
    equipment.manufacturer_model_name,
    equipment.station_name,
    equipment.device_serial_number
from resource
inner join imaging_resource using (resource_id)
inner join resource_to_dicom_equipment on resource_to_dicom_equipment.resource_id=imaging_resource.imaging_resource_id
inner join dicom.equipment on resource_to_dicom_equipment.dicom_equipment_id=equipment.equipment_id;

  name  | manufacturer | manufacturer_model_name |  station_name  | device_serial_number
--------+--------------+-------------------------+----------------+----------------------
 B MR1  | SIEMENS      | Prisma                  | B-MR1 B2PRISMA | 166047
 B MR2  | SIEMENS      | Skyra                   | B-MR2 B2SKY    | 45496
 B MR3  | SIEMENS      | MAGNETOM Sola           | B-MR3 M2SOLA   | 182878
 B MR4  | SIEMENS      | MAGNETOM Vida           | B-MR4 M2VIDA2  | 176027
 B MR5  | SIEMENS      | MAGNETOM Vida           | B-MR5 M2VIDA1  | 175787
 W MR3T | SIEMENS      | TrioTim                 | W-TRIO         | 35201
 P MR3T | SIEMENS      | Verio                   | P-Verio        | 40582
(7 rows)

As you know, it is not uncommon for station names to change, leading potentially to multiple DICOM identities for the same device. If you want to list the various StationName of the aliases for each equipment, you can use the equipment_alias table. For example:

select
    resource.name,
    equipment.manufacturer,
    equipment.manufacturer_model_name,
    equipment.station_name,
    array_agg(distinct equipment_alias.dcm_station_name)
from resource
inner join imaging_resource using (resource_id)
inner join resource_to_dicom_equipment on resource_to_dicom_equipment.resource_id=imaging_resource.imaging_resource_id
inner join dicom.equipment on resource_to_dicom_equipment.dicom_equipment_id=equipment.equipment_id
inner join dicom.equipment_alias using (equipment_id)
group by 1,2,3,4

  name  | manufacturer | manufacturer_model_name |  station_name  |                array_agg
--------+--------------+-------------------------+----------------+-----------------------------------------
 B MR1  | SIEMENS      | Prisma                  | B-MR1 B2PRISMA | {"",AWP166047,BINN2PRISMA}
 B MR2  | SIEMENS      | Skyra                   | B-MR2 B2SKY    | {"",AWP45496,BINN2SKY}
 B MR3  | SIEMENS      | MAGNETOM Sola           | B-MR3 M2SOLA   | {MRI,MRI_3,MRI3}
 B MR4  | SIEMENS      | MAGNETOM Vida           | B-MR4 M2VIDA2  | {MRI,MRI_4,MRI4}
 B MR5  | SIEMENS      | MAGNETOM Vida           | B-MR5 M2VIDA1  | {"",AWP175787,MRI_5}
 P MR3T | SIEMENS      | Verio                   | P-Verio        | {"",MEDPC,MRC40582}
 W MR3T | SIEMENS      | TrioTim                 | W-TRIO         | {"",MRC35201}
(7 rows)

It is important to realize that a same examination may have multiple StudyDescription values. This is the case, for example, in combo exams, that combine series from different studies, e.g. MR Brain w/wo and MR Spine w/wo.

As you saw in the second query of this tutorial, the examination table includes the study_descriptions array column that represents all the study descriptions found across the DICOM series of the examination, ordered by highest frequency of occurrence. You can explore this in your data with the following query:

select
    accession_numbers,
    study_descriptions
from examination
where array_length(study_descriptions,1) >= 2
limit 100;

      accession_numbers       |                                                   study_descriptions
------------------------------+------------------------------------------------------------------------
 {27154386,27156783}          | {"MR-Brain w/o Contrast","MR-Spine Entire w/ + w/o Contrast"}
 {27117805,27117804}          | {"MR-Hip w/o Contrast Left","MR-Hip w/ + w/o Contrast Right"}
 {100860182,100860173}        | {"CT ABDOMEN PELVIS W AND WO CONTRAST","PET/CT LIMITED W DIAGNOSTIC CT"}
 {27211236,27211237}          | {"NM-PET/CT Torso w/ Diagnostic CT","NM-CT Neck/Chest w/ Contrast"}
 {100004543,100004550}        | {"MR ABDOMEN W AND WO CONTRAST","MR BRAIN WO CONTRAST"}
 ...

The StudyDescription typically identifies the general type of study, but not the specific imaging protocol performed.

For example, you might see entries like MR Brain w/wo Contrast, which indicate the general area and contrast usage but do not specify whether it's for a Stroke Protocol, Tumor Protocol, or TBI Protocol. This lack of specificity limits the ability to distinguish between different imaging protocols within the same general study type, and limit the statistical analysis (e.g., the Exam duration of MR Brain w/wo Contrast often ranges from 15min to 120min).

In addition to StudyDescription, the Quantivly ontology introduces the new concept of protocol, accessible via the examination_to_protocol and protocol tables.

Important: Depending on your local setup, you might have different "protocol naming strategies" available in your database, including:

• Rule-based strategies from StudyDescription

• RIS-based naming strategies if you are able to send us protocol information via HL7 messages

• AI-based naming strategy identifying imaging protocols from the acquisition parameters (in R&D)

When joining examination_to_protocol, you always need to remember to select a specific naming strategy in the WHERE clause to avoid multiplying the rows and getting incorrect counts.

Here's how to list the available naming strategies (or "protocol algorithms"):

select distinct
    protocol_algo_id
from protocol

               protocol_algo_id
-----------------------------------------------
 quantivly.protocol.rule-based.no-laterality
 quantivly.protocol.rule-based.keep-laterality
(2 rows)

The two default naming strategies available are:

• quantivly.protocol.rule-based.keep-laterality

• quantivly.protocol.rule-based.no-laterality

Disclaimer: these strategies are based on the StudyDescription field and therefore are not truly represent imaging protocols. However, they can still be quite useful for certain purposes:

• Handling Laterality: The no-laterality strategy removes laterality information from study descriptions. This can be helpful when you want to combine studies that are essentially the same except for laterality. For example, MR Knee Left and MR Knee Right would both be categorized as MR Knee.

• Handling Combo-Exams: For examinations that include multiple studies (combo-exams), all unique StudyDescription values within an examination are concatenated into a single string, ordered alphabetically. This approach helps in grouping and analyzing combo-exams as unified categories.

You can query these naming strategy with:

select
    resource.name,
    accession_numbers,
    protocol.protocol_name
from examination
inner join resource_to_dicom_equipment using (dicom_equipment_id)
inner join resource using (resource_id)
inner join examination_to_protocol using (examination_id)
inner join protocol using (protocol_id)
where extract(year from examination_datetime) = 2024
    and protocol_algo_id = 'quantivly.protocol.rule-based.keep-laterality';

  name  |    accession_numbers    |               protocol_name
--------+---------------------------------+-----------------------------------------------
 B MR1  | {27035086}              | MR-Knee w/o Contrast Left
 B MR2  | {26988826}              | MR-Brain w/o Contrast
 B MR4  | {27103366}              | MR-Arthrogram Upper Extremity Right
 B MR1  | {27089325}              | MR-Craniospinal
 B MR2  | {27214349}              | MR-Upper Extremity Entire w+w/o Cont Lft
 ...

The same with quantivly.protocol.rule-based.no-laterality:

  name  |    accession_numbers    |               protocol_name
--------+-------------------------+-----------------------------------------------
 B MR1  | {27035086}              | MR-Knee w/o Contrast
 B MR2  | {26988826}              | MR-Brain w/o Contrast
 B MR4  | {27103366}              | MR-Arthrogram Upper Extremity
 B MR1  | {27089325}              | MR-Craniospinal
 B MR2  | {27214349}              | MR-Upper Extremity Entire w+w/o Cont
 ...

Always remember: if you join the examination_to_protocol and protocol tables but forget to filter with WHERE protocol_algo_id = '...', then each row will display multiple times, once for each naming strategy. For example, with two naming strategies:

select
    resource.name,
    accession_numbers,
    protocol.protocol_name
from examination
inner join resource_to_dicom_equipment using (dicom_equipment_id)
inner join resource using (resource_id)
inner join examination_to_protocol using (examination_id)
inner join protocol using (protocol_id)
where extract(year from examination_datetime) = 2024

   name  |    accession_numbers    |               protocol_name
 --------+-------------------------+-----------------------------------------------
  B MR1  | {27035086}              | MR-Knee w/o Contrast
  B MR1  | {27035086}              | MR-Knee w/o Contrast
  B MR2  | {26988826}              | MR-Brain w/o Contrast
  B MR2  | {26988826}              | MR-Brain w/o Contrast
  B MR4  | {27103366}              | MR-Arthrogram Upper Extremity
  B MR4  | {27103366}              | MR-Arthrogram Upper Extremity
  B MR1  | {27089325}              | MR-Craniospinal
  B MR1  | {27089325}              | MR-Craniospinal
  B MR2  | {27214349}              | MR-Upper Extremity Entire w+w/o Cont
  B MR2  | {27214349}              | MR-Upper Extremity Entire w+w/o Cont
 ...

To see how these rule-based naming strategies concatenate StudyDescription for combo-exams:

select
    array_length(accession_numbers,1) as n_acc_num,
    protocol.protocol_name
from examination
inner join examination_to_protocol using (examination_id)
inner join protocol using (protocol_id)
where extract(year from examination_datetime) = 2024
    and protocol_algo_id = 'quantivly.protocol.rule-based.keep-laterality'
    and array_length(accession_numbers,1) >= 2;

  n_acc_num | protocol_name 
 -----------+--------------------------------------------------------------------------------------
          2 | MR-Brain w/ + w/o Contrast+MR-Orbits/Face/Neck w/ + w/o Contrast
          2 | MR-Enterography w/ + w/o Contrast+MR-Pelvis w/ + w/o Contrast Body
          2 | CT-Angio Chest w/ Contrast Post Process+CT-Thoracic Spine w/ Contrast
          3 | MR-Leg (Lower) w + w/o Contrast Left+MR-Pelvis w/ + w/o Contrast MSK+MR-Spine Entire
          2 | MR-Chest w/ + w/o Contrast+MR-Orbits/Face/Neck w/ + w/o Contrast
          2 | MR-Chest w/ + w/o Contrast+MR-Orbits/Face/Neck w/ + w/o Contrast
          3 | CT-Chest w/o contrast+CT-Head w/o Contrast+CT-Soft Tissue Neck w/o Contrast
        ...

Note: We found that the number of AccessionNumber may not necessarily correctly match the number of different StudyDescription. Depending on the workflow, technologists may not enter a different AccessionNumber for a different part of a combo exam. However, the StudyDescription typically populated from the scanner template may still change.

The previous rule-based naming strategies do not solve the unability to distinguish between different imaging protocols within the same general study type.

If you are able to send us protocol information in your HL7 messages, then you will be able to retrieve those names using the quantivly.protocol.rule-based.source-ris naming strategy.

We are also working on an AI-based naming strategy identifying imaging protocols from the acquisition parameters. The corresponding naming strategy is quantivly.protocol.graph-based.no-laterality. This is still in R&D, but let us know if you want to test it!

The Quantivly harmonization engine identifies which DICOM series are actual acquisitions (i.e., took time to acquire data) among all the DICOM series available. You can access the acquisition level information by joining the acquisition table. For example the query below retrieve the start and duration of each acquisition:

select
    accession_numbers,
    study_descriptions,
    acquisition_datetime,
    acquisition_duration
from examination
inner join acquisition using (examination_id)
order by examination_datetime desc, acquisition_datetime asc
limit 100;

   accession_numbers   |          study_descriptions           |     acquisition_datetime      | acquisition_duration
-----------------------+---------------------------------------+-------------------------------+----------------------
 {101116204}           | {"MR BRAIN W AND WO CONTRAST"}        | 2024-10-28 03:01:39.8825+00   | 00:00:14
 {101116204}           | {"MR BRAIN W AND WO CONTRAST"}        | 2024-10-28 03:02:35.0275+00   | 00:02:38
 {101116204}           | {"MR BRAIN W AND WO CONTRAST"}        | 2024-10-28 03:05:48.615+00    | 00:01:48
 {101116204}           | {"MR BRAIN W AND WO CONTRAST"}        | 2024-10-28 03:07:51.8625+00   | 00:02:08
 {101116204}           | {"MR BRAIN W AND WO CONTRAST"}        | 2024-10-28 03:10:14.77+00     | 00:02:44
 {101116204}           | {"MR BRAIN W AND WO CONTRAST"}        | 2024-10-28 03:13:03.735+00    | 00:02:02
 {101116204}           | {"MR BRAIN W AND WO CONTRAST"}        | 2024-10-28 03:18:14.2+00      | 00:06:24
 {101116204}           | {"MR BRAIN W AND WO CONTRAST"}        | 2024-10-28 03:24:51.56+00     | 00:02:08
 ...

Each acquisition is linked to a DICOM series dicom.series. For example, you can retrieve the SeriesNumber and SeriesDescription with:

select
    accession_numbers as accession_num,
    acquisition.acquisition_datetime,
    acquisition.acquisition_duration,
    dicom.series.dcm_series_number as series_num,
    dicom.series.dcm_series_description as series_description
from examination
inner join acquisition using (examination_id)
inner join dicom.series using (series_id)
order by examination_datetime desc, acquisition_datetime asc
limit 100;

     accession_num   |     acquisition_datetime      | acquisition_duration | series_num |    series_description
---------------------+-------------------------------+----------------------+------------+-----------------------------
 {101116204}         | 2024-10-28 03:01:39.8825+00   | 00:00:14             |          2 | BRN_LOC_AAHScout
 {101116204}         | 2024-10-28 03:02:35.0275+00   | 00:02:38             |          6 | SAG_T1 MPRAGE (fast) (brain)
 {101116204}         | 2024-10-28 03:05:48.615+00    | 00:01:48             |          9 | AX_T2 drb (fast) (brain)
 {101116204}         | 2024-10-28 03:07:51.8625+00   | 00:02:08             |         10 | AX_T2 FLAIR (fast) (brain)
 {101116204}         | 2024-10-28 03:10:14.77+00     | 00:02:44             |         11 | AX_DTI (fast) (brain)
 {101116204}         | 2024-10-28 03:13:03.735+00    | 00:02:02             |         18 | AX_SWIwave (fast) (brain)_Mag
 {101116204}         | 2024-10-28 03:18:14.2+00      | 00:06:24             |         22 | SAG_T1 SPACE_+C (brain)
 {101116204}         | 2024-10-28 03:24:51.56+00     | 00:02:08             |         25 | AX_T2 FLAIR (fast) (brain) +C
 ...

If you want to list all SeriesDescription of examinations, you can use the following aggregation:

select distinct
  protocol.protocol_name,
  array_agg(
    concat(series.dcm_series_number, '.', series.dcm_series_description) 
    order by acquisition.acquisition_datetime asc
  ) as all_series
from examination
inner join examination_to_protocol using (examination_id)
inner join protocol using (protocol_id)
inner join acquisition using (examination_id)
inner join dicom.series using (series_id)
where 
   protocol_algo_id='quantivly.protocol.rule-based.no-laterality'
group by 1
having count(distinct acquisition_id)<=5    -- With less than 5 acquisitions, for example
limit 50

            protocol_name     |                                     all_series
------------------------------+-------------------------------------------------------------------------------------------
 MR-Brain w/o Contrast        | {2.BRN_LOC_AAHScout,"6.AX_DTI (fast) (brain)","14.AX_T2_dl (fast) (brain)",17.SWI_Images}
 MR-Brain/Vent Check          | {"1.STEALTH_LOC_NO ANGLE","2.AX_T2 HASTE","3.SAG_T2 HASTE","4.COR_T2 HASTE"}
 MR-Brain/Vent Check          | {1.BRN_LOC_AAHScout,"5.AX_T2 HASTE","6.SAG_T2 HASTE","7.COR_T2 HASTE"}
 MR-Pelvis w/o Contrast MSK   | {1.PELVIS_LOC_AAHScout,9.COR_T1_dl,10.COR_T2_FS_dl,11.AX_T2_FS_dl,12.SAG_FSEIR}
 MR-Brain/Vent Check          | {"1.STEALTH_LOC_NO ANGLE","2.AX_T2 HASTE","3.SAG_T2 HASTE","4.COR_T2 HASTE"}
 MR-Cardiac (in Cardiology)   | {101.SURVEY,"201.VCG SSFP SURVEY","301.AXIAL SSFP 20 PHASES"}
 MR-Brain w/o Contrast        | {1.BRN_LOC_AAHScout,5.BRN_LOC_AAHScout,"9.SAG_T1 MPRAGE (fast)"}
 ...

To access the parameters, you need to join dicom.series with the corresponding modality-specific tables, e.g. dicom.mr_series for MR. Then parameters for each MR volume are accessible by joining with dicom.mr_volume. For example, to get the values of EchoTime in MR:

select distinct
  examination.accession_numbers,
  protocol.protocol_name,
  series.dcm_series_number,
  series.dcm_series_description,
  array_agg(dcm_echo_time) as echo_times
from examination
inner join examination_to_protocol using (examination_id)
inner join protocol using (protocol_id)
inner join acquisition using (examination_id)
inner join dicom.series using (series_id)
inner join dicom.mr_series using (series_id)
inner join dicom.mr_volume using (mr_series_id)
where 
  '900781650_ANO' = any(accession_numbers)
  and protocol_algo_id='quantivly.protocol.rule-based.no-laterality'
group by 1,2,3,4

 accession_numbers |    protocol_name    | dcm_series_number | dcm_series_description |    echo_times
-------------------+---------------------+-------------------+------------------------+------------------
 {900781650_ANO}   | MR FOOT WO CONTRAST |                 1 | 3_PL_LOC               | {3.69,3.69,3.69}
 {900781650_ANO}   | MR FOOT WO CONTRAST |                 3 | AX_T2_FS               | {74}
 {900781650_ANO}   | MR FOOT WO CONTRAST |                 4 | SAG_T2_FS              | {52}
 {900781650_ANO}   | MR FOOT WO CONTRAST |                 5 | COR_T1_(FOOTPRINT)     | {33}

NOTE: Above the localizer has 3 echo-times because our harmonization engine not only extrapolates the concept of MR volumes; it also extrapolates the concept of volume stacks. The localizer's 3 different planes have different slice orientations (COR, SAG, AX) which led to three volume stacks and, in turn, 3 values of EchoTime. You can also use array_agg(distinct dcm_echo_time) to only return distinct values.

We hope this tutorial has helped you get started with querying the Quantivly data layer using SQL. There's so much more to discover, and we'll continue to add more examples and advanced topics in future updates.

In the meantime, have fun exploring and experimenting with your own queries. Unlock new insights, dive deeper into your data, and make the most of this powerful tools at your disposal.

Need help or have questions? If you have any questions or need assistance, please don't hesitate to reach out to us at [email protected]. We're here to support you on your data journey.

HAPPY QUERYING!

Classic: Filtering exams with a particular series description

-- First filter exams containing some series description
with exams_with_series as (
  select
    DISTINCT examination.examination_id
  from dicom.series 
  inner join acquisition using (series_id)
  inner join examination using (examination_id)
  where series.dcm_series_description ilike '%bonemri%'  -- << You can change here the filter
    and examination_datetime between
      (now() - '48 month'::interval) and now()           -- << You can change here the date interval
)
-- Then returns various info about those exams
select
  examination.examination_datetime at time zone 'America/New_York' as study_datetime,
  patient.medical_record_number as "patient_mrn",
  patient.birthdate as "patient_dob",
  resource.name as scanner_name,
  equipment.manufacturer_model_name,
  examination.accession_numbers as "accession_numbers",
  examination.study_descriptions as "study_descriptions",
  array_agg(series.dcm_series_number::text || '. ' || series.dcm_series_description
    order by acquisition.acquisition_datetime) as "series_detail"
from exams_with_series
  inner join examination using (examination_id)
  inner join acquisition using (examination_id)
  inner join  dicom.series using (series_id)
  left join resource_to_dicom_equipment using (dicom_equipment_id)
  left join resource using (resource_id)
  left join dicom.equipment_alias using (equipment_alias_id)
  left join dicom.equipment using (equipment_id)
  left outer join dicom.patient on dicom.patient.patient_id = examination.dicom_patient_id
group by examination.examination_datetime,2,3,4,5,6,7
order by examination.examination_datetime asc;