Getting Started: SQL on FHIR (preview)

POST /fhir/$load
Accept: text/yaml
Content-Type: text/yaml

source: 'https://storage.googleapis.com/aidbox-public/synthea/100/all.ndjson.gz'

PUT /ViewDefinition/patient_demo_view
Content-Type: text/yaml
Accept: text/yaml

name: patient_demo_view
status: active
resource: Patient
select:
- column:
  - name: patient_id
    path: id
  - name: gender
    path: gender
  select:
  - forEach: name
    column:
    - name: given
      path: "given.join(' ')"
    - name: family
      path: "family"

name: patient_demo_view
select:
- column:
  - {name: patient_id, path: id}
  - {name: gender, path: gender}
  select:
  - column:
    - {name: given, path: given.join(' ')}
    - {name: family, path: family}
    forEach: name
status: active
resource: patient
id: becb0608-4469-4c09-9990-01972fef5304
resourceType: ViewDefinition
meta: {lastUpdated: '2024-02-05T14:31:02.408994Z', createdAt: '2024-02-05T14:31:02.408994Z', versionId: '936'}

select * from sof.patient_demo_view
limit 10

SELECT jsonb_path_query_first(r.resource, '$ . id') #>> '{}' as "patient_id",
       jsonb_path_query_first(r.resource, '$ . gender') #>> '{}' as "gender"
FROM (SELECT (resource || jsonb_build_object('id', id)) as resource,
              ts,
              cts,
              status
      FROM "patient") as r
LIMIT 10

PUT /fhir/ViewDefinition/condition_demo_view
Content-Type: text/yaml
Accept: text/yaml

name: condition_demo_view
resource: Condition
status: active
select:
- column:
  - name: condition_id
    path: id
  - name: onset
    path: onset.dateTime
  - name: abatement
    path: abatement.dateTime
  - name: status
    path: clinicalStatus.coding.code.first()
  - name: code
    path: code.coding.where(system='http://snomed.info/sct').code.first()
  - name: patient_id
    path: subject.id

name: condition_demo_view
select:
- column:
  - {name: condition_id, path: id}
  - {name: onset, path: onset.dateTime}
  - {name: abatement, path: abatement.dateTime}
  - {name: status, path: clinicalStatus.coding.code.first()}
  - {name: code, path: 'code.coding.where(system=''http://snomed.info/sct'').code.first()'}
  - {name: patient_id, path: subject.id}
status: active
resource: condition
id: deb361b1-2f6d-488e-a80d-aed6c0f4f1b7
resourceType: ViewDefinition
meta: {lastUpdated: '2024-02-01T17:06:44.315877Z', createdAt: '2024-02-01T17:06:44.315877Z', versionId: '908'}

select * from sof.condition_demo_view
limit 10;

SELECT
	cond.id as condition_id,
    jsonb_path_query_first(cond.resource, '$.onset.dateTime') #>> '{}' AS onset,
    jsonb_path_query_first(cond.resource, '$.abatement.dateTime') #>> '{}' AS abatement,
    jsonb_path_query_first(cond.resource, '$.clinicalStatus.coding.code[0]') #>> '{}' AS status,
    jsonb_path_query_first(cond.resource, '$.code.coding ? (@.system == "http://snomed.info/sct").code[0]') #>> '{}' AS code,
    jsonb_path_query_first(cond.resource, '$.subject.id') #>> '{}' AS patient_id
FROM Condition AS cond
limit 10

SELECT *
FROM sof.patient_demo_view pt
JOIN sof.condition_demo_view cond using (patient_id)
WHERE code = '840539006' and max_text_date_bound(cond.abatement) > (NOW() - interval '1 year')

SELECT
	pt.id AS patient_id,
    array_to_string(
      (
        SELECT array_agg(x)
        FROM jsonb_array_elements_text(
          jsonb_path_query_array(name.element, '$.given[*]')
        ) AS x
      ), ' ') AS given,
    jsonb_path_query_first(name.element, '$.family') #>> '{}' AS family,
    jsonb_path_query_first(pt.resource, '$.gender') #>> '{}' AS gender,
    cond.id as condition_id,
    jsonb_path_query_first(cond.resource, '$.onset.dateTime') #>> '{}' AS onset,
    jsonb_path_query_first(cond.resource, '$.abatement.dateTime') #>> '{}' AS abatement,
    jsonb_path_query_first(cond.resource, '$.clinicalStatus.coding.code[0]') #>> '{}' AS status,
    jsonb_path_query_first(cond.resource, '$.code.coding ? (@.system == "http://snomed.info/sct").code[0]') #>> '{}' AS code
FROM Patient AS pt
JOIN Condition cond ON jsonb_path_query_first(cond.resource, '$.subject.id') #>> '{}' = pt.id
JOIN LATERAL jsonb_path_query(pt.resource, '$.name [*]') name(element) ON true
WHERE
	jsonb_path_query_first(cond.resource, '$.code.coding ? (@.system == "http://snomed.info/sct").code[0]') #>> '{}' = '840539006'
    AND max_text_date_bound(jsonb_path_query_first(cond.resource, '$.abatement.dateTime') #>> '{}') > (NOW() - interval '1 year')

PUT /ViewDefinition/condition
content-type: text/yaml
accept: text/yaml

name: condition
resource: Condition
status: active

select:
- column:
  - name: condition_id
    path: id
  - name: patient_id
    path: subject.getId()
  - name: onset
    path: onset.dateTime
  - name: abatement
    path: abatement.dateTime
- forEach: code.coding
  column:
  - name: code
    path: code
  - name: system
    path: system
  - name: display
    path: display

name: condition
select:
- column:
  - {name: condition_id, path: id}
  - {name: patient_id, path: subject.getId()}
  - {name: onset, path: onset.dateTime}
  - {name: abatement, path: abatement.dateTime}
- column:
  - {name: code, path: code}
  - {name: system, path: system}
  - {name: display, path: display}
  forEach: code.coding
status: active
resource: Condition
id: 375f8fcf-ca4e-4e56-b161-fd795e1c2149
resourceType: ViewDefinition
meta: {lastUpdated: '2024-02-05T14:32:25.110123Z', createdAt: '2024-02-05T14:32:25.110123Z', versionId: '940'}

select * from sof.condition
limit 10;

PUT /ViewDefinition/patient
content-type: text/yaml
accept: text/yaml

name: patient
resource: Patient
status: active
select:
- column:
  - name: patient_id
    path: id
  - path: name.first().family
    name: family
  - name: given
    path: name.first().given.join(' ')
  - name: dob
    path: birthDate
  - path: identifier.where(system = 'http://hl7.org/fhir/sid/us-ssn').value
    name: patient_ssn

name: patient
select:
- column:
  - {name: patient_id, path: id}
  - {name: family, path: name.first().family}
  - {name: given, path: name.first().given.join(' ')}
  - {name: dob, path: birthDate}
  - {name: patient_ssn, path: 'identifier.where(system = ''http://hl7.org/fhir/sid/us-ssn'').value'}
status: active
resource: Patient
id: 99d8e7ce-c706-44f1-a551-f52044cd946b
resourceType: ViewDefinition
meta: {lastUpdated: '2024-02-05T14:32:32.074425Z', createdAt: '2024-02-05T14:32:32.074425Z', versionId: '942'}

select * from sof.patient
limit 10

-- Find most popular conditon for specific patient cohort

with patient_cohort as (
  select
    pt.patient_id patient_id,
    floor(DATE_PART('YEAR', AGE(NOW(), pt.dob::date))/10)*10 min_age
  from sof.patient pt
),
patient_ages as (
  select distinct min_age
  from patient_cohort
),
patient_condition as (
  select
    pc.min_age min_age,
    cond.code code,
    count(cond.code) amount
  from patient_cohort pc
  join sof.condition cond using (patient_id)
  group by pc.min_age, cond.code
)
select min_age,
  (select pc2.amount from patient_condition pc2
   where pc2.min_age = pc.min_age
   order by pc2.amount desc
   limit 1) amount,
  (select pc2.code from patient_condition pc2
   where pc2.min_age = pc.min_age
   order by pc2.amount desc
   limit 1) code
from patient_ages pc
order by pc.min_age desc

select pt.given,
       pt.dob,
       cond.onset
from sof.patient pt
join sof.condition cond on cond.patient_id = pt.patient_id
where pt.dob > '1970-01-01'
  and cond.code = '10509002'
  and cond.system = 'http://snomed.info/sct'
  and cond.onset > '2018-01-01'
limit 100;