Query data from flat views

Using flat views

Once you've saved your View Definition, the corresponding flat view will be created in a database in sof schema. For example, to select all the rows from a view named patient_view you'll need a query like this:

select * from sof.patient_view

Note that in the current version of Aidbox, View Definitions can be materialized as SQL views only.

From here, you can use your flat views however you like. Popular use cases include building complex queries for data analysis and using BI-tools to build dashboards.

Real-world examples

Get all patients diagnosed with COVID after a specified date

To find all patients who was born after or in 1970 and who were diagnosed with COVID after or in 2021, you'll need to define 2 views.

Patient View

{
  "name": "patient_view",
  "resource": "Patient",
  "status": "active",
  "description": "Patient flat view",
  "select": [
    {
      "column": [
        {
          "name": "id",
          "path": "id"
        },
        {
          "name": "name",
          "path": "name[0].given.join(' ')"
        },
        {
          "name": "birthDate",
          "path": "birthDate"
        }
      ]
    }
  ]
}

Condition View

{
  "name": "condition_view",
  "resource": "Condition",
  "status": "active",
  "select": [
    {
      "column": [
        {
          "name": "id",
          "path": "id"
        },
        {
          "name": "pid",
          "path": "subject.getId('Patient')"
        },
        {
          "name": "date",
          "path": "recordedDate"
        }
      ]
    },
    {
      "forEach": "code.coding",
      "column": [
        {
          "name": "code",
          "path": "code"
        },
        {
          "name": "system",
          "path": "system"
        }
      ]
    }
  ]
}

With these views defined, you can query the information you need with the following query.

SQL Query

select pt.name,
       pt.birthDate,
       cond.date
from sof.patient_view pt
join sof.condition_view cond on cond.pid = pt.id
where pt.birthDate > '1970-01-01'
  and cond.code = '840539006'
  and cond.system = 'http://snomed.info/sct'
  and cond.date > '2021-01-01'
limit 100;

Possible output

We use synthetic data for the purpose of this demonstration.

name	birthdate	date
Rae Lucie	2021-02-11	2021-03-31T12:50:15

All patients who had an encounter in a specified period in a given location

To find all times patients had an encounter in 2020 or later in a location managed by a given organization you'll need to define 3 views.

Patient view

{
  "name": "patient_view",
  "resource": "Patient",
  "description": "Patient flat view",
  "status": "active",
  "select": [
    {
      "column": [
        {
          "name": "id",
          "path": "id"
        },
        {
          "name": "name",
          "path": "name[0].given.join(' ')"
        }
      ]
    }
  ]
}

Location view

{
  "name": "location_view",
  "resource": "Location",
  "status": "active",
  "select": [
    {
      "column": [
        {
          "name": "id",
          "path": "id"
        },
        {
          "name": "name",
          "path": "name"
        },
        {
          "name": "org_id",
          "path": "managingOrganization.identifier.value"
        }
      ]
    }
  ]
}

Encounter view

{
  "name": "encounter_view",
  "resource": "Encounter",
  "status": "active",
  "select": [
    {
      "column": [
        {
          "name": "id",
          "path": "id"
        },
        {
          "name": "subject_id",
          "path": "subject.getId('Patient')"
        },
        {
          "name": "location_id",
          "path": "location[0].location.getId('Location')"
        },
        {
          "name": "start_time",
          "path": "period.start"
        }
      ]
    }
  ]
}

Then, your query will look like this.

SQL query

select pt.name,
       loc.name location,
       enc.start_time
from sof.patient_view pt
join sof.encounter_view enc on enc.subject_id = pt.id
join sof.location_view loc on enc.location_id = loc.id
where enc.start_time > '2020-01-01'
  and loc.org_id = '74ab949d-17ac-3309-83a0-13b4405c66aa'
limit 100;

Possible output

We use synthetic data for the purpose of this demonstration.

name	location	start_time
Holli Casey	Fitchburg Outpatient Clinic	2021-12-04T16:58:15
Holli Casey	Fitchburg Outpatient Clinic	2021-12-04T17:28:14
Lonnie Kristofer	Fitchburg Outpatient Clinic	2020-12-24T19:54:09