SQL on FHIR
2.1.0-pre - release International flag

SQL on FHIR, published by SQL on FHIR Working Group. This guide is not an authorized publication; it is the continuous build for version 2.1.0-pre built by the FHIR (HL7® FHIR® Standard) CI Build. This version is based on the current content of https://github.com/FHIR/sql-on-fhir-v2/ and changes regularly. See the Directory of published versions

Example Binary: PatientAndContactAddressUnion

{
  "resourceType" : "https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition",
  "id" : "PatientAndContactAddressUnion",
  "name" : "patient_and_contact_addresses",
  "status" : "draft",
  "resource" : "Patient",
  "select" : [
    {
      "column" : [
        {
          "path" : "getResourceKey()",
          "name" : "resource_id"
        }
      ],
      "unionAll" : [
        {
          "column" : [
            {
              "path" : "line.join('\n')",
              "name" : "street"
            },
            {
              "path" : "city",
              "name" : "city"
            },
            {
              "path" : "postalCode",
              "name" : "zip"
            },
            {
              "path" : "true",
              "name" : "is_patient"
            }
          ],
          "forEach" : "address"
        },
        {
          "column" : [
            {
              "path" : "line.join('\n')",
              "name" : "street"
            },
            {
              "path" : "city",
              "name" : "city"
            },
            {
              "path" : "postalCode",
              "name" : "zip"
            },
            {
              "path" : "false",
              "name" : "is_patient"
            }
          ],
          "forEach" : "contact.address"
        }
      ]
    }
  ]
}

Notes:

This will result in a “patient_and_contact_addresses” table that looks like this:

resource_id street city zip is_patient
1 123 Main St\nApt 1 San Diego 92101 true
1 456 Maplewood Dve\nApt 2 New York 10001 true
1 456 Maplewood Dve\nApt 2 New York 10001 false
2 789 Brookside Ave\nApt 3 Los Angeles 90001 true
2 987 Pinehurst Rd\nApt 4 Chicago 60601 false