Getting Started: SQL on FHIR

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

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

format: fhir
resources: {CarePlan: 356, Observation: 20382, MedicationAdministration: 150, Goal: 301, Patient: 124, DiagnosticReport: 1430, Practitioner: 181, ExplanationOfBenefit: 3460, Immunization: 1636, Claim: 4488, MedicationRequest: 1028, Encounter: 3460, Condition: 871, Procedure: 2854, Organization: 181, AllergyIntolerance: 40, ImagingStudy: 134}

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

name: patient_demo_view
status: active
resource: Patient
url: http://example.org/viewdefinition/patient_demo_view
select:
  - column:
      - name: patient_id
        path: id
      - name: gender
        path: gender
      - name: dob
        path: birthDate
      - name: patient_ssn
        path: "identifier.where(system = 'http://hl7.org/fhir/sid/us-ssn').value"
    select:
      - forEach: name
        column:
          - name: given
            path: "given.join(' ')"
          - name: family
            path: family

name: patient_demo_view
status: active
resource: Patient
url: http://example.org/viewdefinition/patient_demo_view
select:
- column:
  - {name: patient_id, path: id}
  - {name: gender, path: gender}
  - {name: dob, path: birthDate}
  - {name: patient_ssn, path: 'identifier.where(system = ''http://hl7.org/fhir/sid/us-ssn'').value'}
  select:
  - forEach: name
    column:
    - {name: given, path: given.join(' ')}
    - {name: family, path: family}
id: patient_demo_view
resourceType: ViewDefinition

http://example.org/viewdefinition/patient_demo_view

POST /fhir/ViewDefinition/patient_demo_view/$materialize
Content-Type: application/json
Accept: application/json

{
  "resourceType": "Parameters",
  "parameter": [
    {
      "name": "type",
      "valueCode": "view"
    }
  ]
}

{
  "resourceType": "Parameters",
  "parameter": [
    {
      "name": "viewName",
      "valueString": "sof.patient_demo_view"
    },
    {
      "name": "viewType",
      "valueString": "view"
    },
    {
      "name": "viewSchema",
      "valueString": "sof"
    }
  ]
}

select * from sof.patient_demo_view
limit 10

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

name: condition_demo_view
url: http://example.org/viewdefinition/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: display
    path: code.text
  - name: patient_id
    path: subject.id

name: condition_demo_view
url: http://example.org/viewdefinition/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: display, path: code.text}
  - {name: patient_id, path: subject.id}
id: condition_demo_view
resourceType: ViewDefinition

POST /fhir/ViewDefinition/condition_demo_view/$materialize
Content-Type: application/json
Accept: application/json

{
  "resourceType": "Parameters",
  "parameter": [
    {
      "name": "type",
      "valueCode": "view"
    }
  ]
}

{
  "resourceType": "Parameters",
  "parameter": [
    {
      "name": "viewName",
      "valueString": "sof.condition_demo_view"
    },
    {
      "name": "viewType",
      "valueString": "view"
    },
    {
      "name": "viewSchema",
      "valueString": "sof"
    }
  ]
}

select * from sof.condition_demo_view
limit 10;

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

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]') #>> '{}' = '195662009'
    AND max_text_date_bound(jsonb_path_query_first(cond.resource, '$.abatement.dateTime') #>> '{}') > (NOW() - interval '20 years')

SELECT *
FROM sof.patient_demo_view pt
JOIN sof.condition_demo_view cond using (patient_id)
WHERE code = '444814009' and pt.dob >= '1970-01-01' and cond.abatement >= '2018-01-01'

SELECT code, display, count (*) from sof.condition_demo_view
GROUP BY 1,2
ORDER BY 3 desc
limit 10