For AI agents: the documentation index is at /docs/aidbox/llms.txt. A Markdown version of this page is available at /docs/aidbox/notebooks.md or by requesting it with the Accept: text/markdown header.
Aidbox Docs
All notebooks

SQL on FHIR (fhiranalytics talk by Nikolai)

Description

47 cells · updated May 13, 2025

Run in Aidbox
This is a read-only view. Responses shown were saved when the notebook was published — open it in your own Aidbox to run the cells against live data.

What is FHIR-native Database & Server?

SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
truncate patient;
0 rows affected
REST Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
POST /fhir/$load
Accept: text/yaml
Content-Type: text/yaml

source: 'https://storage.googleapis.com/aidbox-public/synthea/v2/10000/fhir/Patient.ndjson.gz'
Response: Body
Status: 200 
format: fhir
resources: {Patient: 11199}
REST Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
GET /fhir/Patient?_count=5&name=Keebler
Accept: text/yaml
Content-Type: text/yaml
Response: Body
Status: 200 
resourceType: Bundle
type: searchset
meta: {versionId: '0'}
total: 30
link:
- {relation: first, url: 'https://abnotebooks.aidbox.app/fhir/Patient?_count=5&name=Keebler&page=1'}
- {relation: self, url: 'https://abnotebooks.aidbox.app/fhir/Patient?_count=5&name=Keebler&page=1'}
- {relation: next, url: 'https://abnotebooks.aidbox.app/fhir/Patient?_count=5&name=Keebler&page=2'}
- {relation: last, url: 'https://abnotebooks.aidbox.app/fhir/Patient?_count=5&name=Keebler&page=6'}
entry:
- resource:
    multipleBirthBoolean: false
    address:
    - city: Harwich Port
      line: [405 Connelly Gateway Suite 10]
      state: MA
      country: US
      extension:
      - url: http://hl7.org/fhir/StructureDefinition/geolocation
        extension:
        - {url: latitude, valueDecimal: 41.70643073403296}
        - {url: longitude, valueDecimal: -70.05275919979205}
      postalCode: '02646'
    meta:
      profile: ['http://hl7.org/fhir/us/core/StructureDefinition/us-core-patient']
      lastUpdated: '2025-05-13T09:50:09.087776Z'
      versionId: '1213'
      extension:
      - {url: 'https://fhir.aidbox.app/fhir/StructureDefinition/created-at', valueInstant: '2025-05-13T09:50:09.087776Z'}
    name:
    - use: official
      given: [Laquanda, Jeanine]
      family: Keebler
      prefix: [Mrs.]
    - use: maiden
      given: [Laquanda, Jeanine]
      family: Schaefer
      prefix: [Mrs.]
    birthDate: '1978-05-05'
    resourceType: Patient
    extension:
    - url: http://hl7.org/fhir/us/core/StructureDefinition/us-core-race
      extension:
      - url: ombCategory
        valueCoding: {code: 2106-3, system: 'urn:oid:2.16.840.1.113883.6.238', display: White}
      - {url: text, valueString: White}
    - url: http://hl7.org/fhir/us/core/StructureDefinition/us-core-ethnicity
      extension:
      - url: ombCategory
        valueCoding: {code: 2186-5, system: 'urn:oid:2.16.840.1.113883.6.238', display: Not Hispanic or Latino}
      - {url: text, valueString: Not Hispanic or Latino}
    - {url: 'http://hl7.org/fhir/StructureDefinition/patient-mothersMaidenName', valueString: Farrah Hettinger}
    - {url: 'http://hl7.org/fhir/us/core/StructureDefinition/us-core-birthsex', valueCode: F}
    - url: http://hl7.org/fhir/StructureDefinition/patient-birthPlace
      valueAddress: {city: Reading, state: Massachusetts, country: US}
    - {url: 'http://synthetichealth.github.io/synthea/disability-adjusted-life-years', valueDecimal: 0.17120668814129922}
    - {url: 'http://synthetichealth.github.io/synthea/quality-adjusted-life-years', valueDecimal: 44.8287933118587}
    communication:
    - language:
        text: English
        coding:
        - {code: en-US, system: 'urn:ietf:bcp:47', display: English}
    id: 1a713715-c55f-4aa9-c2cb-9862503d0587
    identifier:
    - {value: 1a713715-c55f-4aa9-c2cb-9862503d0587, system: 'https://github.com/synthetichealth/synthea'}
    - type:
        text: Medical Record Number
        coding:
        - {code: MR, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Medical Record Number}
      value: 1a713715-c55f-4aa9-c2cb-9862503d0587
      system: http://hospital.smarthealthit.org
    - type:
        text: Social Security Number
        coding:
        - {code: SS, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Social Security Number}
      value: 999-71-5317
      system: http://hl7.org/fhir/sid/us-ssn
    - type:
        text: Driver's License
        coding:
        - {code: DL, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Driver's License}
      value: S99958080
      system: urn:oid:2.16.840.1.113883.4.3.25
    - type:
        text: Passport Number
        coding:
        - {code: PPN, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Passport Number}
      value: X77195209X
      system: http://standardhealthrecord.org/fhir/StructureDefinition/passportNumber
    telecom:
    - {use: home, value: 555-279-8040, system: phone}
    gender: female
    maritalStatus:
      text: M
      coding:
      - {code: M, system: 'http://terminology.hl7.org/CodeSystem/v3-MaritalStatus', display: M}
    text: {div: '<div xmlns="http://www.w3.org/1999/xhtml">Generated by <a href="https://github.com/synthetichealth/synthea">Synthea</a>.Version identifier: synthea-java .   Person seed: -3564040794817598940  Population seed: 123123123</div>', status: generated}
  search: {mode: match}
  fullUrl: https://abnotebooks.aidbox.app/Patient/1a713715-c55f-4aa9-c2cb-9862503d0587
  link:
  - {relation: self, url: 'https://abnotebooks.aidbox.app/Patient/1a713715-c55f-4aa9-c2cb-9862503d0587'}
- resource:
    multipleBirthBoolean: false
    address:
    - city: Rockland
      line: [618 Herzog Course Suite 21]
      state: MA
      country: US
      extension:
      - url: http://hl7.org/fhir/StructureDefinition/geolocation
        extension:
        - {url: latitude, valueDecimal: 42.17775051098648}
        - {url: longitude, valueDecimal: -70.91541568169306}
      postalCode: '00000'
    meta:
      profile: ['http://hl7.org/fhir/us/core/StructureDefinition/us-core-patient']
      lastUpdated: '2025-05-13T09:50:09.087776Z'
      versionId: '1213'
      extension:
      - {url: 'https://fhir.aidbox.app/fhir/StructureDefinition/created-at', valueInstant: '2025-05-13T09:50:09.087776Z'}
    name:
    - use: official
      given: [Mariko, Jayna]
      family: Keebler
      prefix: [Ms.]
    birthDate: '1985-07-19'
    resourceType: Patient
    extension:
    - url: http://hl7.org/fhir/us/core/StructureDefinition/us-core-race
      extension:
      - url: ombCategory
        valueCoding: {code: 2106-3, system: 'urn:oid:2.16.840.1.113883.6.238', display: White}
      - {url: text, valueString: White}
    - url: http://hl7.org/fhir/us/core/StructureDefinition/us-core-ethnicity
      extension:
      - url: ombCategory
        valueCoding: {code: 2186-5, system: 'urn:oid:2.16.840.1.113883.6.238', display: Not Hispanic or Latino}
      - {url: text, valueString: Not Hispanic or Latino}
    - {url: 'http://hl7.org/fhir/StructureDefinition/patient-mothersMaidenName', valueString: Florance Donnelly}
    - {url: 'http://hl7.org/fhir/us/core/StructureDefinition/us-core-birthsex', valueCode: F}
    - url: http://hl7.org/fhir/StructureDefinition/patient-birthPlace
      valueAddress: {city: Braintree, state: Massachusetts, country: US}
    - {url: 'http://synthetichealth.github.io/synthea/disability-adjusted-life-years', valueDecimal: 0.49617067232793066}
    - {url: 'http://synthetichealth.github.io/synthea/quality-adjusted-life-years', valueDecimal: 37.50382932767207}
    communication:
    - language:
        text: English
        coding:
        - {code: en-US, system: 'urn:ietf:bcp:47', display: English}
    id: 1e4b3104-dccd-68f8-820b-384342e2b2e0
    identifier:
    - {value: 1e4b3104-dccd-68f8-820b-384342e2b2e0, system: 'https://github.com/synthetichealth/synthea'}
    - type:
        text: Medical Record Number
        coding:
        - {code: MR, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Medical Record Number}
      value: 1e4b3104-dccd-68f8-820b-384342e2b2e0
      system: http://hospital.smarthealthit.org
    - type:
        text: Social Security Number
        coding:
        - {code: SS, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Social Security Number}
      value: 999-84-6941
      system: http://hl7.org/fhir/sid/us-ssn
    - type:
        text: Driver's License
        coding:
        - {code: DL, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Driver's License}
      value: S99951023
      system: urn:oid:2.16.840.1.113883.4.3.25
    - type:
        text: Passport Number
        coding:
        - {code: PPN, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Passport Number}
      value: X85086783X
      system: http://standardhealthrecord.org/fhir/StructureDefinition/passportNumber
    telecom:
    - {use: home, value: 555-596-6379, system: phone}
    gender: female
    maritalStatus:
      text: S
      coding:
      - {code: S, system: 'http://terminology.hl7.org/CodeSystem/v3-MaritalStatus', display: S}
    text: {div: '<div xmlns="http://www.w3.org/1999/xhtml">Generated by <a href="https://github.com/synthetichealth/synthea">Synthea</a>.Version identifier: synthea-java .   Person seed: 9206187544687154673  Population seed: 123123123</div>', status: generated}
  search: {mode: match}
  fullUrl: https://abnotebooks.aidbox.app/Patient/1e4b3104-dccd-68f8-820b-384342e2b2e0
  link:
  - {relation: self, url: 'https://abnotebooks.aidbox.app/Patient/1e4b3104-dccd-68f8-820b-384342e2b2e0'}
- resource:
    multipleBirthBoolean: false
    address:
    - city: Framingham
      line: [828 Little Brook]
      state: MA
      country: US
      extension:
      - url: http://hl7.org/fhir/StructureDefinition/geolocation
        extension:
        - {url: latitude, valueDecimal: 42.28578415054004}
        - {url: longitude, valueDecimal: -71.45472994467438}
      postalCode: '01701'
    meta:
      profile: ['http://hl7.org/fhir/us/core/StructureDefinition/us-core-patient']
      lastUpdated: '2025-05-13T09:50:09.087776Z'
      versionId: '1213'
      extension:
      - {url: 'https://fhir.aidbox.app/fhir/StructureDefinition/created-at', valueInstant: '2025-05-13T09:50:09.087776Z'}
    name:
    - use: official
      given: [Kasha, Maisha]
      family: Keebler
      prefix: [Ms.]
    birthDate: '2004-01-31'
    resourceType: Patient
    extension:
    - url: http://hl7.org/fhir/us/core/StructureDefinition/us-core-race
      extension:
      - url: ombCategory
        valueCoding: {code: 2106-3, system: 'urn:oid:2.16.840.1.113883.6.238', display: White}
      - {url: text, valueString: White}
    - url: http://hl7.org/fhir/us/core/StructureDefinition/us-core-ethnicity
      extension:
      - url: ombCategory
        valueCoding: {code: 2186-5, system: 'urn:oid:2.16.840.1.113883.6.238', display: Not Hispanic or Latino}
      - {url: text, valueString: Not Hispanic or Latino}
    - {url: 'http://hl7.org/fhir/StructureDefinition/patient-mothersMaidenName', valueString: Sherilyn Labadie}
    - {url: 'http://hl7.org/fhir/us/core/StructureDefinition/us-core-birthsex', valueCode: F}
    - url: http://hl7.org/fhir/StructureDefinition/patient-birthPlace
      valueAddress: {city: Groton, state: Massachusetts, country: US}
    - {url: 'http://synthetichealth.github.io/synthea/disability-adjusted-life-years', valueDecimal: 0.0}
    - {url: 'http://synthetichealth.github.io/synthea/quality-adjusted-life-years', valueDecimal: 19.0}
    communication:
    - language:
        text: English
        coding:
        - {code: en-US, system: 'urn:ietf:bcp:47', display: English}
    id: 2ca3274b-fa25-9727-3d3e-06990cbeedf5
    identifier:
    - {value: 2ca3274b-fa25-9727-3d3e-06990cbeedf5, system: 'https://github.com/synthetichealth/synthea'}
    - type:
        text: Medical Record Number
        coding:
        - {code: MR, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Medical Record Number}
      value: 2ca3274b-fa25-9727-3d3e-06990cbeedf5
      system: http://hospital.smarthealthit.org
    - type:
        text: Social Security Number
        coding:
        - {code: SS, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Social Security Number}
      value: 999-89-6930
      system: http://hl7.org/fhir/sid/us-ssn
    - type:
        text: Driver's License
        coding:
        - {code: DL, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Driver's License}
      value: S99990444
      system: urn:oid:2.16.840.1.113883.4.3.25
    - type:
        text: Passport Number
        coding:
        - {code: PPN, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Passport Number}
      value: X46546174X
      system: http://standardhealthrecord.org/fhir/StructureDefinition/passportNumber
    telecom:
    - {use: home, value: 555-673-7167, system: phone}
    gender: female
    maritalStatus:
      text: Never Married
      coding:
      - {code: S, system: 'http://terminology.hl7.org/CodeSystem/v3-MaritalStatus', display: Never Married}
    text: {div: '<div xmlns="http://www.w3.org/1999/xhtml">Generated by <a href="https://github.com/synthetichealth/synthea">Synthea</a>.Version identifier: synthea-java .   Person seed: 8853927195172530176  Population seed: 123123123</div>', status: generated}
  search: {mode: match}
  fullUrl: https://abnotebooks.aidbox.app/Patient/2ca3274b-fa25-9727-3d3e-06990cbeedf5
  link:
  - {relation: self, url: 'https://abnotebooks.aidbox.app/Patient/2ca3274b-fa25-9727-3d3e-06990cbeedf5'}
- resource:
    multipleBirthBoolean: false
    address:
    - city: Rockport
      line: [579 Barrows Spur]
      state: MA
      country: US
      extension:
      - url: http://hl7.org/fhir/StructureDefinition/geolocation
        extension:
        - {url: latitude, valueDecimal: 42.6903780817117}
        - {url: longitude, valueDecimal: -70.65900613061429}
      postalCode: '01966'
    meta:
      profile: ['http://hl7.org/fhir/us/core/StructureDefinition/us-core-patient']
      lastUpdated: '2025-05-13T09:50:09.087776Z'
      versionId: '1213'
      extension:
      - {url: 'https://fhir.aidbox.app/fhir/StructureDefinition/created-at', valueInstant: '2025-05-13T09:50:09.087776Z'}
    name:
    - use: official
      given: [Sun, Merna]
      family: Keebler
      prefix: [Ms.]
    birthDate: '1919-01-11'
    resourceType: Patient
    extension:
    - url: http://hl7.org/fhir/us/core/StructureDefinition/us-core-race
      extension:
      - url: ombCategory
        valueCoding: {code: 2106-3, system: 'urn:oid:2.16.840.1.113883.6.238', display: White}
      - {url: text, valueString: White}
    - url: http://hl7.org/fhir/us/core/StructureDefinition/us-core-ethnicity
      extension:
      - url: ombCategory
        valueCoding: {code: 2186-5, system: 'urn:oid:2.16.840.1.113883.6.238', display: Not Hispanic or Latino}
      - {url: text, valueString: Not Hispanic or Latino}
    - {url: 'http://hl7.org/fhir/StructureDefinition/patient-mothersMaidenName', valueString: Kevin Langworth}
    - {url: 'http://hl7.org/fhir/us/core/StructureDefinition/us-core-birthsex', valueCode: F}
    - url: http://hl7.org/fhir/StructureDefinition/patient-birthPlace
      valueAddress: {city: Natick, state: Massachusetts, country: US}
    - {url: 'http://synthetichealth.github.io/synthea/disability-adjusted-life-years', valueDecimal: 10.113820020799455}
    - {url: 'http://synthetichealth.github.io/synthea/quality-adjusted-life-years', valueDecimal: 93.88617997920055}
    communication:
    - language:
        text: English
        coding:
        - {code: en-US, system: 'urn:ietf:bcp:47', display: English}
    id: 2f3301b4-e870-2423-e1c7-6be346094af0
    identifier:
    - {value: 2f3301b4-e870-2423-e1c7-6be346094af0, system: 'https://github.com/synthetichealth/synthea'}
    - type:
        text: Medical Record Number
        coding:
        - {code: MR, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Medical Record Number}
      value: 2f3301b4-e870-2423-e1c7-6be346094af0
      system: http://hospital.smarthealthit.org
    - type:
        text: Social Security Number
        coding:
        - {code: SS, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Social Security Number}
      value: 999-41-9388
      system: http://hl7.org/fhir/sid/us-ssn
    - type:
        text: Driver's License
        coding:
        - {code: DL, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Driver's License}
      value: S99965168
      system: urn:oid:2.16.840.1.113883.4.3.25
    - type:
        text: Passport Number
        coding:
        - {code: PPN, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Passport Number}
      value: X26213091X
      system: http://standardhealthrecord.org/fhir/StructureDefinition/passportNumber
    telecom:
    - {use: home, value: 555-638-7147, system: phone}
    gender: female
    maritalStatus:
      text: S
      coding:
      - {code: S, system: 'http://terminology.hl7.org/CodeSystem/v3-MaritalStatus', display: S}
    text: {div: '<div xmlns="http://www.w3.org/1999/xhtml">Generated by <a href="https://github.com/synthetichealth/synthea">Synthea</a>.Version identifier: synthea-java .   Person seed: -346869613856591437  Population seed: 123123123</div>', status: generated}
  search: {mode: match}
  fullUrl: https://abnotebooks.aidbox.app/Patient/2f3301b4-e870-2423-e1c7-6be346094af0
  link:
  - {relation: self, url: 'https://abnotebooks.aidbox.app/Patient/2f3301b4-e870-2423-e1c7-6be346094af0'}
- resource:
    multipleBirthBoolean: false
    address:
    - city: Lynn
      line: [187 Denesik Stravenue]
      state: MA
      country: US
      extension:
      - url: http://hl7.org/fhir/StructureDefinition/geolocation
        extension:
        - {url: latitude, valueDecimal: 42.47815432344032}
        - {url: longitude, valueDecimal: -70.97482450304817}
      postalCode: '01904'
    meta:
      profile: ['http://hl7.org/fhir/us/core/StructureDefinition/us-core-patient']
      lastUpdated: '2025-05-13T09:50:09.087776Z'
      versionId: '1213'
      extension:
      - {url: 'https://fhir.aidbox.app/fhir/StructureDefinition/created-at', valueInstant: '2025-05-13T09:50:09.087776Z'}
    name:
    - use: official
      given: [Matthew, Beverly]
      family: Keebler
    birthDate: '2020-10-19'
    resourceType: Patient
    extension:
    - url: http://hl7.org/fhir/us/core/StructureDefinition/us-core-race
      extension:
      - url: ombCategory
        valueCoding: {code: 2106-3, system: 'urn:oid:2.16.840.1.113883.6.238', display: White}
      - {url: text, valueString: White}
    - url: http://hl7.org/fhir/us/core/StructureDefinition/us-core-ethnicity
      extension:
      - url: ombCategory
        valueCoding: {code: 2186-5, system: 'urn:oid:2.16.840.1.113883.6.238', display: Not Hispanic or Latino}
      - {url: text, valueString: Not Hispanic or Latino}
    - {url: 'http://hl7.org/fhir/StructureDefinition/patient-mothersMaidenName', valueString: Kelsi Waters}
    - {url: 'http://hl7.org/fhir/us/core/StructureDefinition/us-core-birthsex', valueCode: F}
    - url: http://hl7.org/fhir/StructureDefinition/patient-birthPlace
      valueAddress: {city: Beverly, state: Massachusetts, country: US}
    - {url: 'http://synthetichealth.github.io/synthea/disability-adjusted-life-years', valueDecimal: 0.0}
    - {url: 'http://synthetichealth.github.io/synthea/quality-adjusted-life-years', valueDecimal: 3.0}
    communication:
    - language:
        text: English
        coding:
        - {code: en-US, system: 'urn:ietf:bcp:47', display: English}
    id: 349911f5-4a1e-218e-5d60-4371f7af8bea
    identifier:
    - {value: 349911f5-4a1e-218e-5d60-4371f7af8bea, system: 'https://github.com/synthetichealth/synthea'}
    - type:
        text: Medical Record Number
        coding:
        - {code: MR, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Medical Record Number}
      value: 349911f5-4a1e-218e-5d60-4371f7af8bea
      system: http://hospital.smarthealthit.org
    - type:
        text: Social Security Number
        coding:
        - {code: SS, system: 'http://terminology.hl7.org/CodeSystem/v2-0203', display: Social Security Number}
      value: 999-59-5954
      system: http://hl7.org/fhir/sid/us-ssn
    telecom:
    - {use: home, value: 555-421-2355, system: phone}
    gender: female
    maritalStatus:
      text: Never Married
      coding:
      - {code: S, system: 'http://terminology.hl7.org/CodeSystem/v3-MaritalStatus', display: Never Married}
    text: {div: '<div xmlns="http://www.w3.org/1999/xhtml">Generated by <a href="https://github.com/synthetichealth/synthea">Synthea</a>.Version identifier: synthea-java .   Person seed: 5576412232321417033  Population seed: 123123123</div>', status: generated}
  search: {mode: match}
  fullUrl: https://abnotebooks.aidbox.app/Patient/349911f5-4a1e-218e-5d60-4371f7af8bea
  link:
  - {relation: self, url: 'https://abnotebooks.aidbox.app/Patient/349911f5-4a1e-218e-5d60-4371f7af8bea'}
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select count(*) from patient limit 1
Result
count
11199
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select * from patient limit 1
Result
idtsctstxidstatusresourceresource_type
000111d4-3efc-fcc2-25e4-49c31b62f42c2025-05-13T09:50:09Z2025-05-13T09:50:09Z1213created{"address":[{"city":"East Bridgewater","line":["1051 Pollich Vale"],"state":"MA","country":"US","extension":[{"url":"http://hl7.org/fhir/StructureDefinition/geolocation","extension":[{"url":"latitude","value":{"decimal":42.06751495546451}},{"url":"longitude","value":{"decimal":-70.96342549452409}}]}],"postalCode":"00000"}],"meta":{"profile":["http://hl7.org/fhir/us/core/StructureDefinition/us-core-patient"]},"name":[{"use":"official","given":["Stewart","Eldridge"],"family":"Lind","prefix":["Mr."]}],"birthDate":"1988-03-16","multipleBirth":{"boolean":false},"extension":[{"url":"http://hl7.org/fhir/us/core/StructureDefinition/us-core-race","extension":[{"url":"ombCategory","value":{"Coding":{"code":"2106-3","system":"urn:oid:2.16.840.1.113883.6.238","display":"White"}}},{"url":"text","value":{"string":"White"}}]},{"url":"http://hl7.org/fhir/us/core/StructureDefinition/us-core-ethnicity","extension":[{"url":"ombCategory","value":{"Coding":{"code":"2186-5","system":"urn:oid:2.16.840.1.113883.6.238","display":"Not Hispanic or Latino"}}},{"url":"text","value":{"string":"Not Hispanic or Latino"}}]},{"url":"http://hl7.org/fhir/StructureDefinition/patient-mothersMaidenName","value":{"string":"Kari D'Amore"}},{"url":"http://hl7.org/fhir/us/core/StructureDefinition/us-core-birthsex","value":{"code":"M"}},{"url":"http://hl7.org/fhir/StructureDefinition/patient-birthPlace","value":{"Address":{"city":"Brockton","state":"Massachusetts","country":"US"}}},{"url":"http://synthetichealth.github.io/synthea/disability-adjusted-life-years","value":{"decimal":0.9873779951321696}},{"url":"http://synthetichealth.github.io/synthea/quality-adjusted-life-years","value":{"decimal":34.01262200486783}}],"communication":[{"language":{"text":"English","coding":[{"code":"en-US","system":"urn:ietf:bcp:47","display":"English"}]}}],"identifier":[{"value":"000111d4-3efc-fcc2-25e4-49c31b62f42c","system":"https://github.com/synthetichealth/synthea"},{"type":{"text":"Medical Record Number","coding":[{"code":"MR","system":"http://terminology.hl7.org/CodeSystem/v2-0203","display":"Medical Record Number"}]},"value":"000111d4-3efc-fcc2-25e4-49c31b62f42c","system":"http://hospital.smarthealthit.org"},{"type":{"text":"Social Security Number","coding":[{"code":"SS","system":"http://terminology.hl7.org/CodeSystem/v2-0203","display":"Social Security Number"}]},"value":"999-99-9143","system":"http://hl7.org/fhir/sid/us-ssn"},{"type":{"text":"Driver's License","coding":[{"code":"DL","system":"http://terminology.hl7.org/CodeSystem/v2-0203","display":"Driver's License"}]},"value":"S99912674","system":"urn:oid:2.16.840.1.113883.4.3.25"},{"type":{"text":"Passport Number","coding":[{"code":"PPN","system":"http://terminology.hl7.org/CodeSystem/v2-0203","display":"Passport Number"}]},"value":"X33798525X","system":"http://standardhealthrecord.org/fhir/StructureDefinition/passportNumber"}],"telecom":[{"use":"home","value":"555-215-8911","system":"phone"}],"gender":"male","maritalStatus":{"text":"M","coding":[{"code":"M","system":"http://terminology.hl7.org/CodeSystem/v3-MaritalStatus","display":"M"}]},"text":{"div":"<div xmlns=\"http://www.w3.org/1999/xhtml\">Generated by <a href=\"https://github.com/synthetichealth/synthea\">Synthea</a>.Version identifier: synthea-java . Person seed: 3750074215496009103 Population seed: 123123123</div>","status":"generated"}}Patient

How to query jsonb?

SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select id
     , resource#>>'{name,0,family}' as family
     , resource#>>'{name,0,given,0}' as given
     , resource#>>'{birthDate}' as birthDate
     , extract(year from age(now(), (resource#>>'{birthDate}')::date)) as age
from patient
order by 5 desc
limit 5
Result
idagegivenfamilybirthdate
b3a2f141-528a-bee3-3050-572bbcfa0860111RosamondRatke1913-09-16
dcb5de8a-b17e-a721-6e29-89fe08e04bb4111IsauraKeebler1913-09-16
6518e6e8-b633-aa8b-a5a1-c2f1df2c7b37111DomoniqueSmith1913-09-16
6ce27836-8979-8c8f-850f-5744725de18b111AnjaGrady1913-09-16
e02af67b-ffdd-092a-48c6-eb947b2d3f05111RenayCrona1913-10-17

Use ViewDefinition Builder to see details of translation!

SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
drop view sof.patient cascade
0 rows affected

POST ViewDefinition to FHIR Server to create the view

REST Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
PUT /ViewDefinition/patient
Content-Type: text/yaml
Accept: text/yaml

name: patient
status: active
resource: Patient
select:
- column:
  - name: patient_id
    path: id
  - name: gender
    path: gender
  - name: dob
    path: birthDate
Response: Body
Status: 201 
name: patient
select:
- column:
  - {name: patient_id, path: id}
  - {name: gender, path: gender}
  - {name: dob, path: birthDate}
status: active
resource: Patient
id: patient
resourceType: ViewDefinition
meta: {lastUpdated: '2025-05-13T09:50:58.604790Z', createdAt: '2025-05-13T09:50:58.604790Z', versionId: '1219'}

The view will be created in sof schema!

SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select * from sof.patient limit 10
Result
dobgenderpatient_id
1988-03-16male000111d4-3efc-fcc2-25e4-49c31b62f42c
2008-01-24male004cdba3-6a1c-0b6b-6d2e-b97d8a0cf5bc
1953-09-25male0002faef-649f-aa06-cfd1-0ecc1ec99235
2005-12-07male0005fe04-55b6-c7c3-52d3-b76c74499d87
2002-10-16female0007aebd-55a5-d08a-b2d0-7f95674d299f
2004-10-16female0008a839-4c6d-6ebf-ecce-f1df6d086f90
1962-11-29male00135e42-11b0-8671-7958-5285dd07cc43
2017-11-16female004eaf62-fc11-86a4-9065-32a9f13aabd6
2001-01-10male0017d214-3884-46a7-5ec4-03dd48bd67f5
2024-02-05female001994b4-835b-3b18-7faa-e46335536b66

Let's take a look what is the definition of this view

SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
SELECT pg_get_viewdef('sof.patient', true);
Result
pg_get_viewdef
SELECT id AS patient_id, jsonb_path_query_first(resource, '$."gender"'::jsonpath) #>> '{}'::text[] AS gender, jsonb_path_query_first(resource, '$."birthDate"'::jsonpath) #>> '{}'::text[] AS dob FROM patient r;

How database query this view

SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
explain analyze 
select * from sof.patient 
where dob = '1988-03-16' limit 10
Result
query plan
Limit (cost=0.00..1583.85 rows=10 width=101) (actual time=0.029..24.229 rows=2 loops=1)
-> Seq Scan on patient r (cost=0.00..8869.54 rows=56 width=101) (actual time=0.028..24.224 rows=2 loops=1)
Filter: ((jsonb_path_query_first(resource, '$."birthDate"'::jsonpath, '{}'::jsonb, false) #>> '{}'::text[]) = '1988-03-16'::text)
Rows Removed by Filter: 11197
Planning Time: 0.140 ms
Execution Time: 24.268 ms
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
drop view sof.patient_plus
0 rows affected
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
create or replace view sof.patient_plus  AS 
select *, DATE_PART('year', AGE(CURRENT_DATE, (dob)::date)) as age 
from sof.patient
0 rows affected
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select * from sof.patient_plus limit 10
Result
agedobgenderpatient_id
361988-03-16male000111d4-3efc-fcc2-25e4-49c31b62f42c
162008-01-24male004cdba3-6a1c-0b6b-6d2e-b97d8a0cf5bc
711953-09-25male0002faef-649f-aa06-cfd1-0ecc1ec99235
182005-12-07male0005fe04-55b6-c7c3-52d3-b76c74499d87
222002-10-16female0007aebd-55a5-d08a-b2d0-7f95674d299f
202004-10-16female0008a839-4c6d-6ebf-ecce-f1df6d086f90
611962-11-29male00135e42-11b0-8671-7958-5285dd07cc43
62017-11-16female004eaf62-fc11-86a4-9065-32a9f13aabd6
232001-01-10male0017d214-3884-46a7-5ec4-03dd48bd67f5
02024-02-05female001994b4-835b-3b18-7faa-e46335536b66
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select age, count(*) from sof.patient_plus 
group by 1
order by 2 desc
limit 10
Result
agecount
54188
50181
53174
55174
58169
59168
30163
45162
18159
21158

Use it in grafana

SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
truncate condition
0 rows affected
REST Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
POST /fhir/$load
Accept: text/yaml
Content-Type: text/yaml

source: 'https://storage.googleapis.com/aidbox-public/synthea/v2/10000/fhir/Condition.ndjson.gz'
Response: Body
Status: 200 
format: fhir
resources: {Condition: 500542}
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select count(*) from condition
Result
count
500542
REST Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
PUT /ViewDefinition/condition
Content-Type: text/yaml
Accept: text/yaml

status: active
resource: Condition
name: condition
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: category
    path: category.first().coding.display.first()
Response: Body
Status: 200 
id: condition
name: condition
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: category, path: category.first().coding.display.first()}
status: active
resource: Condition
resourceType: ViewDefinition
meta: {lastUpdated: '2024-10-30T07:48:53.804286Z', createdAt: '2024-10-29T22:51:01.956392Z', versionId: '759'}
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select count(*) from sof.condition
Result
count
500542
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select * from sof.condition limit 10;
Result
codeonsetstatusdisplaycategoryabatementpatient_idcondition_id
1609030071973-04-06T13:43:15+00:00resolvedFull-time employment (finding)Encounter Diagnosis1974-04-12T13:46:24+00:00e799dfef-949b-a7bf-bf64-69dae5c940f6eeb3b12c-5117-8b58-02a4-ddef182bdf21
1609040011983-12-09T19:49:34+00:00resolvedPart-time employment (finding)Encounter Diagnosis1984-01-13T19:38:52+00:007ade9a3a-f477-e9c2-d15c-6374623bebe3eeb3b853-04b8-d233-5021-2ee61585e909
1609030072015-04-12T17:48:39+00:00resolvedFull-time employment (finding)Encounter Diagnosis2015-09-06T18:14:59+00:00aed263be-4545-907f-730d-84657ae5592beeb3de17-ff84-882e-b7ab-0f4fd140ad1c
1609030072023-02-28T01:30:33+00:00resolvedFull-time employment (finding)Encounter Diagnosis2024-03-05T01:31:37+00:005d785fc3-5a6d-1dd8-7166-b90b5d7db11ceeb3e4b9-249a-b21f-1645-ecab7d7524a5
1956620092021-02-27T01:57:17+00:00resolvedAcute viral pharyngitis (disorder)Encounter Diagnosis2021-03-12T00:57:17+00:007a72c1d1-43a3-bd86-4b85-3630022b5aa2eeb3f38a-94be-7ed0-802b-7cb82c83e959
1609030072009-09-15T23:16:48+00:00resolvedFull-time employment (finding)Encounter Diagnosis2009-09-29T23:17:16+00:004ba64e80-dc22-7c48-d124-0232594ec141eeb41ddf-2db0-4380-b2b2-895563651fc9
4318550051995-09-06T19:03:17+00:00activeChronic kidney disease stage 1 (disorder)Encounter Diagnosis5d20feac-4bb5-1542-a9c1-b363ff0366c0eeb42d15-bf50-8127-4b61-093e424de620
1609040011998-09-04T04:58:28+00:00resolvedPart-time employment (finding)Encounter Diagnosis1998-10-02T04:38:10+00:0064cc3eae-0827-504b-45ba-cb0e411646a7eeb4632f-236a-33bf-d913-3d82dc725d92
1609030072019-05-19T22:53:07+00:00resolvedFull-time employment (finding)Encounter Diagnosis2020-05-24T22:35:53+00:0078b74eba-1467-0881-948d-b37f4f1241cfeeb46b91-f29b-ef8a-b69b-e7ef517d8739
1609040012013-06-06T00:46:32+00:00resolvedPart-time employment (finding)Encounter Diagnosis2015-05-14T00:59:56+00:0061ac89cc-9174-22a2-cc53-98d0184a798beeb470ff-6a70-f419-3990-0c4f57e3dbc5

Let's join

SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select  c.display, count(*)
from sof.patient_plus p, sof.condition c
where p.patient_id = c.patient_id
and c.code not in ('160903007', '73595000','160904001')
and status = 'active'
and age between 60 and 65
group by 
c.display
order by count(*) desc
limit 10
Result
countdisplay
665Body mass index 30+ - obesity (finding)
526Prediabetes
503Hypertension
480Received higher education (finding)
436Anemia (disorder)
294Has a criminal record (finding)
294Metabolic syndrome X (disorder)
262Chronic sinusitis (disorder)
261Abnormal findings diagnostic imaging heart+coronary circulat (finding)
260Received certificate of high school equivalency (finding)
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
truncate encounter;
0 rows affected
REST Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
POST /fhir/$load
Accept: text/yaml
Content-Type: text/yaml

source: 'https://storage.googleapis.com/aidbox-public/synthea/v2/10000/fhir/Encounter.ndjson.gz'
Response: Body
Status: 200 
format: fhir
resources: {Encounter: 791987}
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select count(*) from encounter
Result
count
791987
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
drop view sof.encounter cascade
0 rows affected
REST Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
PUT /ViewDefinition/encounter
Content-Type: text/yaml
Accept: text/yaml


name: encounter
status: draft
resource: Encounter
select:
- column:
  - name: id
    path: id
    type: id
  - name: patient_id
    path: subject.id
    type: id
  - name: period_start
    path: period.start
  - name: period_end
    path: period.end
  - name: class
    path: class.code
  - name: status
    path: status
  - name: type_display
    path: type[0].coding.where(system='http://snomed.info/sct').display
  - name: type_code
    path: type[0].coding.where(system='http://snomed.info/sct').code
Response: Body
Status: 200 
id: encounter
name: encounter
select:
- column:
  - {name: id, path: id, type: id}
  - {name: patient_id, path: subject.id, type: id}
  - {name: period_start, path: period.start}
  - {name: period_end, path: period.end}
  - {name: class, path: class.code}
  - {name: status, path: status}
  - {name: type_display, path: 'type[0].coding.where(system=''http://snomed.info/sct'').display'}
  - {name: type_code, path: 'type[0].coding.where(system=''http://snomed.info/sct'').code'}
status: draft
resource: Encounter
resourceType: ViewDefinition
meta: {lastUpdated: '2024-10-30T09:20:14.732995Z', createdAt: '2024-10-30T07:15:30.138060Z', versionId: '772'}
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select * from sof.encounter limit 10
Result
idpidclassstatustype_codeperiod_endperiod_starttype_display
0000094e-c51f-7e42-f8d7-398c3b3dda2d8145dbeb-a011-02b1-edb3-e57f63340624AMBfinished1853490032004-12-19T23:25:02+00:002004-12-19T22:45:30+00:00Encounter for check up (procedure)
00000a91-2cdd-24c1-ed93-ea6854f566a3a9baa13d-fc7b-00c7-e716-646c78cdfbf6AMBfinished7029270042018-09-03T16:25:24+00:002018-09-03T15:29:48+00:00Urgent care clinic (procedure)
00003fd1-b3bd-76cf-b13a-5d887cab6016ffcce8f7-0618-8586-17a9-b178abe4f30fAMBfinished3167440092010-03-13T12:08:53+00:002010-03-13T11:53:53+00:00Office Visit
0000470b-e361-cd45-42ff-b1a74079574d60be2e62-ad73-1ab6-4326-0d831867785cAMBfinished1853470012014-12-09T05:14:33+00:002014-12-09T01:34:33+00:00Encounter for problem (procedure)
0000dce3-33fa-483c-e649-700d127a7ebcbcfc5329-81d1-4197-3089-badaf40a9020EMERfinished508490022014-11-28T22:12:41+00:002014-11-28T21:12:41+00:00Emergency Encounter
0000e354-b536-74e6-52fa-1d46c057a84e671d4d76-fc45-a8ae-4acc-015f0ed49137AMBfinished1853450091947-09-21T18:38:58+00:001947-09-21T18:23:58+00:00Encounter for symptom
00012e48-d87c-56fc-1225-49ab60252f9aeacb9bc0-3f75-6a32-a8d6-7b8ba2700a0aAMBfinished1853490032020-06-16T09:15:55+00:002020-06-16T09:00:55+00:00Encounter for 'check-up'
0001355f-ef5e-1d60-893b-42e50a6cbfe54b626d0a-9d86-eaea-5d95-ddee0e4d5eb6AMBfinished7029270041988-05-01T04:29:23+00:001988-05-01T03:42:41+00:00Urgent care clinic (procedure)
000140bf-20f4-71f2-ea65-a5a49b7a5ef29b38a367-0a12-a682-4c1a-9c42dfabc894AMBfinished7029270041995-06-02T22:18:26+00:001995-06-02T21:48:16+00:00Urgent care clinic (procedure)
0001484c-8caa-51a5-6d36-0185a42b6515b8796c0f-7b58-6215-7a28-5445075caa19AMBfinished4106200092015-02-27T06:22:24+00:002015-02-27T06:07:24+00:00Well child visit (procedure)
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
select substr(period_start, 1,7), class, count(*) 
from sof.encounter 
group by 1,2 order by 1 desc  limit 10
Result
classcountsubstr
AMB2152024-07
EMER122024-07
HH12024-07
IMP22024-07
AMB34752024-06
EMER2122024-06
HH72024-06
IMP572024-06
VR282024-06
AMB35352024-05
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
explain analyze
select substr(period_start, 1,7), class, count(*) 
from sof.encounter 
group by 1,2 order by 1 desc  limit 10
Result
query plan
Limit (cost=249076.89..249078.28 rows=10 width=72) (actual time=568.328..572.159 rows=10 loops=1)
-> Finalize GroupAggregate (cost=249076.89..358750.61 rows=791988 width=72) (actual time=561.728..565.556 rows=10 loops=1)
Group Key: (substr((jsonb_path_query_first(r.resource, '$."period"."start"'::jsonpath, '{}'::jsonb, false) #>> '{}'::text[]), 1, 7)), ((jsonb_path_query_first(r.resource, '$."class"."code"'::jsonpath, '{}'::jsonb, false) #>> '{}'::text[]))
-> Gather Merge (cost=249076.89..335980.95 rows=659990 width=72) (actual time=561.698..565.528 rows=28 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial GroupAggregate (cost=248076.87..258801.71 rows=329995 width=72) (actual time=529.427..545.072 rows=244 loops=3)
Group Key: (substr((jsonb_path_query_first(r.resource, '$."period"."start"'::jsonpath, '{}'::jsonb, false) #>> '{}'::text[]), 1, 7)), ((jsonb_path_query_first(r.resource, '$."class"."code"'::jsonpath, '{}'::jsonb, false) #>> '{}'::text[]))
-> Sort (cost=248076.87..248901.86 rows=329995 width=64) (actual time=529.379..538.118 rows=63342 loops=3)
Sort Key: (substr((jsonb_path_query_first(r.resource, '$."period"."start"'::jsonpath, '{}'::jsonb, false) #>> '{}'::text[]), 1, 7)) DESC, ((jsonb_path_query_first(r.resource, '$."class"."code"'::jsonpath, '{}'::jsonb, false) #>> '{}'::text[]))
Sort Method: external merge Disk: 5936kB
Worker 0: Sort Method: external merge Disk: 5616kB
Worker 1: Sort Method: external merge Disk: 5592kB
-> Parallel Seq Scan on encounter r (cost=0.00..205421.89 rows=329995 width=64) (actual time=2.953..335.690 rows=263996 loops=3)
Planning Time: 0.127 ms
JIT:
Functions: 22
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 1.462 ms, Inlining 0.000 ms, Optimization 0.710 ms, Emission 14.690 ms, Total 16.861 ms
Execution Time: 573.209 ms

Materialize tables for performance

SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
drop table sof.encounter_materialized;
create unlogged table sof.encounter_ut as select * from sof.encounter order by period_start, patient_id
0 rows affected
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
explain analyze
select substr(period_start, 1,7), class, count(*) 
from sof.encounter_ut 
group by 1,2 order by 1 desc  limit 10
Result
query plan
Limit (cost=65326.94..65328.25 rows=10 width=44) (actual time=179.435..185.579 rows=10 loops=1)
-> Finalize GroupAggregate (cost=65326.94..163246.59 rows=749249 width=44) (actual time=179.434..185.576 rows=10 loops=1)
Group Key: (substr(period_start, 1, 7)), class
-> Gather Merge (cost=65326.94..148931.05 rows=659990 width=44) (actual time=179.426..185.565 rows=20 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial GroupAggregate (cost=64326.91..71751.80 rows=329995 width=44) (actual time=124.904..147.812 rows=262 loops=3)
Group Key: (substr(period_start, 1, 7)), class
-> Sort (cost=64326.91..65151.90 rows=329995 width=36) (actual time=124.842..139.479 rows=88889 loops=3)
Sort Key: (substr(period_start, 1, 7)) DESC, class
Sort Method: external merge Disk: 6832kB
Worker 0: Sort Method: external merge Disk: 5392kB
Worker 1: Sort Method: external merge Disk: 4920kB
-> Parallel Seq Scan on encounter_ut (cost=0.00..25052.93 rows=329995 width=36) (actual time=0.013..46.634 rows=263996 loops=3)
Planning Time: 0.098 ms
Execution Time: 186.248 ms
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
drop materialized view sof.encounter_mv;
create  materialized view sof.encounter_mv as select * from sof.encounter order by period_start, patient_id
0 rows affected
SQL Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
explain analyze
select substr(period_start, 1,7), class, count(*) 
from sof.encounter_mv 
group by 1,2 order by 1 desc  limit 10
Result
query plan
Limit (cost=30368.77..30371.37 rows=10 width=72) (actual time=139.022..141.914 rows=10 loops=1)
-> Finalize GroupAggregate (cost=30368.77..40802.75 rows=40000 width=72) (actual time=139.021..141.912 rows=10 loops=1)
Group Key: (substr(period_start, 1, 7)), class
-> Gather Merge (cost=30368.77..39702.75 rows=80000 width=72) (actual time=139.016..141.905 rows=26 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=29368.74..29468.74 rows=40000 width=72) (actual time=128.089..128.119 rows=913 loops=3)
Sort Key: (substr(period_start, 1, 7)) DESC, class
Sort Method: quicksort Memory: 239kB
Worker 0: Sort Method: quicksort Memory: 127kB
Worker 1: Sort Method: quicksort Memory: 125kB
-> Partial HashAggregate (cost=25811.20..26311.20 rows=40000 width=72) (actual time=125.674..126.048 rows=2121 loops=3)
Group Key: substr(period_start, 1, 7), class
Batches: 1 Memory Usage: 1809kB
Worker 0: Batches: 1 Memory Usage: 1681kB
Worker 1: Batches: 1 Memory Usage: 1681kB
-> Parallel Seq Scan on encounter_mv (cost=0.00..23980.00 rows=244160 width=64) (actual time=0.040..87.737 rows=263996 loops=3)
Planning Time: 0.085 ms
Execution Time: 142.325 ms

Turn it into AidboxQuery

REST Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
PUT /AidboxQuery/encounters_report
Accept: text/yaml
Content-Type: text/yaml

resourceType: AidboxQuery
query:  select substr(period_start, 1,7), class, count(*) from sof.encounter_mv group by 1,2 order by 1 desc  limit 10
Response: Body
Status: 201 
id: encounters_report
query: select substr(period_start, 1,7), class, count(*) from sof.encounter_mv group by 1,2 order by 1 desc  limit 10
resourceType: AidboxQuery
meta: {lastUpdated: '2024-10-30T12:42:33.909509Z', createdAt: '2024-10-30T12:42:33.909509Z', versionId: '906'}
REST Send Cells run only inside Aidbox. Use Run in Aidbox above to open this notebook in your own instance. 
GET /$query/encounters_report
Response: Body
Status: 200 
{
  "data": [
    {
      "substr": "2024-07",
      "class": "AMB",
      "count": 215
    },
    {
      "substr": "2024-07",
      "class": "EMER",
      "count": 12
    },
    {
      "substr": "2024-07",
      "class": "HH",
      "count": 1
    },
    {
      "substr": "2024-07",
      "class": "IMP",
      "count": 2
    },
    {
      "substr": "2024-06",
      "class": "AMB",
      "count": 3475
    },
    {
      "substr": "2024-06",
      "class": "EMER",
      "count": 212
    },
    {
      "substr": "2024-06",
      "class": "HH",
      "count": 7
    },
    {
      "substr": "2024-06",
      "class": "IMP",
      "count": 57
    },
    {
      "substr": "2024-06",
      "class": "VR",
      "count": 28
    },
    {
      "substr": "2024-05",
      "class": "AMB",
      "count": 3535
    }
  ],
  "query": [
    "select substr(period_start, 1,7), class, count(*) from sof.encounter_mv group by 1,2 order by 1 desc  limit 10"
  ]
}