Queries

Overview

Queries are made using JSON and used for:

1. real-time querying of a specific dataset
2. As the building block of concepts and cohorts

The query is submitted as a dictionary with 4 key-value pairs:

• table: the table in the dataset you want to query
• column: the column in the table you want to query
• operator: the operator that relates the value to the column
  possible values: { '=', '>', '<', '>=', '<=', '<>', '!=', 'IN', 'NOT IN', 'LIKE', 'NOT LIKE' }
• value: the value with which to query the column in regards to the operator
  We then translate this into a SQL query.

For example the following query:

{
  "table": "allergy",
  "column": "description",
  "operator": "=",
  "value": "peanut butter"
}

gets converted into the SQL query:

SELECT * FROM allergy WHERE allergy.description = 'peanut butter';

We provide the sql query for concepts and cohorts to help the user debug any potential issues.

Using LIKE Queries

The SQL LIKE operator allows for wildcard values to be included in the "value" field:

• % represents zero, one, or multiple characters
• _ represents a single character

LIKE operators are very useful when querying hierarchical columns such as ICD codes. For example, instead of listing all ICD-10 codes that represent Cerebral Infarction, we can search for patients who've had an ICD-10 code that begins with I63 using the query below.

{'table': 'condition', 'column': 'code', 'operator': 'like', 'value': 'I63%'}

Why do we structure queries in this way?

Alone, these simple queries ensure the user can query specific tables getting real-time feedback as they think about creating concepts and cohorts.

Together these queries form the basis of concepts and cohorts allowing a simple framework for users to interact with.

We are continually working to provide more flexibility in how users can interact with the data, if you have any specific ideas or concerns please reach out to us at [email protected]