LifeOmic FHIR Service SQL

LifeOmic FHIR Service SQL provides a SQL interface to query your FHIR resources. It's built for developers and data analysts familiar with standard SQL.

SQL is part of the LifeOmic FHIR Service DSL (Domain Specific Language). See the Quick Start tutorial to get started.

You can also consult the FHIR Search API Documentation.

Quick Start

Using the core FHIR API, SQL is executed using the HTTP header Content-Type: text/plain. For example, to select patient resources, see the example request below:

Example Request:

POST /v1/fhir-search/projects/${project} HTTP/1.1
Host: api.us.lifeomic.com
LifeOmic-Account: ${account}
LifeOmic-User: ${user}
Authorization: Bearer ${token}
Content-Type: text/plain

SELECT * FROM patient

This will retrieve the first 10 Patient resources available in your project.

Additional information about the response can be found in the DSL Response section.

Example Response:

{
    "took": 6,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 1,
            "relation": "eq"
        },
        "max_score": 1,
        "hits": [
            {
                "_index": "patient-2020.03.11-000001",
                "_type": "_doc",
                "_id": "bHJuOmxvOnVzOmxpZmVvbWljOnJlc291cmNlOmRhYmJhODBlLWU3NDgtNGUzZC1iOTlhLTM5YzAxMGRhNDAxYwo=",
                "_score": 1,
                "_source": {
                    "resourceType": "Patient",
                    "id": "dabba80e-e748-4e3d-b99a-39c010da401c",
                    "active": true,
                    "name": [
                        {
                            "use": "official",
                            "family": "Chalmers",
                            "given": ["Peter", "James"]
                        }
                    ],
                    "telecom": [
                        {
                            "system": "phone",
                            "value": "(03) 5555 6473",
                            "use": "work",
                            "rank": 1
                        }
                    ],
                    "gender": "male",
                    "birthDate": "1974-12-25",
                    "deceasedBoolean": false,
                    "address": [
                        {
                            "use": "home",
                            "type": "both",
                            "text": "534 Erewhon St PeasantVille, Rainbow, Vic  3999",
                            "line": ["534 Erewhon St"],
                            "city": "PleasantVille",
                            "district": "Rainbow",
                            "state": "Vic",
                            "postalCode": "3999",
                            "period": {
                                "start": "1974-12-25"
                            }
                        }
                    ]
                }
            }
        ]
    }
}

The SQL Language

LifeOmic FHIR Service SQL supports a subset of standard ANSI SQL

Data Types

See the LifeOmic FHIR Service DSL section on Data Types.

Statements

SELECT Statement

SELECT
    [ALL]
    select_expr [, select_expr] ...
    [FROM table_references]
    [WHERE where_condition]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

The SELECT statement is used to retrieve rows from a table.

The list of select_expr terms indicates which columns to retrieve.

• A select list consisting only of a single unqualified * can be used as shorthand to select all columns from a table:

SELECT * FROM t1

• A select list containing a select_expr can be used to select a column from a table.

SELECT `id.keyword` FROM patient

• A select list may contain multiple select_expr

SELECT `id.keyword`, telecom FROM patient

FROM

The FROM table_references clause indicates the table to retrieve rows from.

WHERE

The WHERE clause indicates the condition or conditions that rows must satisfy to be returned.

LIMIT

The LIMIT clause can be used to limit the number of rows returned. It takes one or two nonnegative numeric integer constants.

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM t1 LIMIT 100;   # Retrieve rows 1-100

SELECT * FROM t1 LIMIT 10,10; # Retrieve rows 11-20