Nested select options

[rbrush]

Here is an attempt at incorporating some of the FQL's approach into the view definitions. This is just a quick pass at what our current examples might look like in this model, with the following major changes:

• This removes the vars stanza in favor of nested expressions. I think this simplifies most cases, although there is some redundancy in the blood pressure example so we could consider whether it's worth keeping vars even in this nested world.
• This removes the explicit whenMany expression, since the unrolling behavior is dictated by the use of foreach or from in nested selects.
• Having multiple foreaches in different trees will result in a cross join, similar to FQL and nested SQL behavior. However, I think that's easier to see and understand here than in our previous structure, so it is less likely to lead to surprising behavior for the user.

Simple demographics

The simple demographics example is unchanged, although this adds a "select" stanza around columns to create a parent for nested
selects.

We could also consider just keeping a "columns" option at the root, but having all of them under "selects" is arguably
more consistent with the examples below.

{
  "name": "patient_demographics",
  "resource": "Patient",
  "desc": "A view of simple patient demographics",
  "select" [{
      "name": "id", 
      "expr": "id"
    },{
      "name": "gender", 
      "expr": "gender"
    },{
      // Use the first 'official' patient name for our demographics table.
      // Selects within this stanza are relative to the `from` result.
      "from": "name.where(use = 'official').first()",
      "select" [{
        "name": "given_name",
        // Use the FHIRPath join function to create a single given name field.
        "expr": "given.join(' ')",
      },{
        "name": "family_name",
        "expr": "family",
      }]
    }
  ]
}

Patient addresses

The nest example unnests patient addresses. Note that there is an "unnest", explicitly unrolling the address into separate rows.
We

{
  "name": "patient_address",
  "resource": "Patient",
  "select": [{
    "name": "patient_id", 
    "expr": "Patient.id"
  },{
    // "foreach" rather than "from" to indicate we are unrolling these into separate rows
    "foreach": "address"
    "select": [{
      "name": "street",
      // Join all address lines together for this simple table.
      "expr": "line.join('\n')",
      "desc": "The full street address, including newlines if present."
    },{
      "name": "use",
      "expr": "use",
    },{
      "name": "city",
      "expr": "city",
    },{
      "name": "zip",
      "expr": "postalCode",
    }]
  }]
}

Blood pressures

{
  "name": "us_core_blood_pressure",
  "resource": "Observation",
  "select": [
    // Selects the columns relative to the resource root, since there is no "from" expression"
    {"name": "id", "expr": "id"},
    {"name": "patient_id", "expr": "subject.getId()"},
    {"name": "effective_date_time", "expr": "effective.ofType(dateTime)"},
    // Nested selects to retrieve items from specific locations within the resource. Since this is "select" 
    // rather than "unnest", it will not unroll into multiple rows.
    {
      // Select columns relative to the "from" expression.
      "from": "component.where(code.coding.exists(system='http://loinc.org' and code='8480-6')).first()",
      "select": [
        {"name": "sbp_quantity_system",  "expr": "value.ofType(Quantity).system"},
        {"name": "sbp_quantity_code",  "expr": "value.ofType(Quantity).code"},
        {"name": "sbp_quantity_display",  "expr": "value.ofType(Quantity).unit"},
        {"name": "sbp_quantity_value",  "expr": "value.ofType(Quantity).value"}]
    },{
      "from": "component.where(code.coding.exists(system='http://loinc.org' and code='8462-4')).first()",
      "select": [
        {"name": "dbp_quantity_system",  "expr": "value.ofType(Quantity).system"},
        {"name": "dbp_quantity_code",  "expr": "value.ofType(Quantity).code"},
        {"name": "dbp_quantity_display",  "expr": "value.ofType(Quantity).unit"},
        {"name": "dbp_quantity_value",  "expr": "value.ofType(Quantity).value"}]
      }]
  // filter to blood pressure observations without a data absent reason 
  // for the systolic or diastolic component
  "where": [
    {"expr": "code.coding.exists(system='http://loinc.org' and code='85354-9')"},
    {"expr": "component.where(code.coding.exists(system='http://loinc.org' and code='8480-6')).first().dataAbsentReason.empty()"},
    {"expr": "component.where(code.coding.exists(system='http://loinc.org' and code='8462-4')).first().dataAbsentReason.empty()"}]
}

[niquola]

I like it! To make the definition more regular - can we rename "resource" on the top-level to "from"?!

[niquola]

And to make it more SQL-lish "filters" => "where" :)

[niquola]

Here is a version only using select (no columns):

{name: "patient_address",
 from: "Patient",
 select: [{name: "pid", expr: "Patient.id"},
          {from: "address"
            select: [{name: "street", expr: "line.join('\n')"},
                          {name: "use",    expr: "use"},
                          {name: "city",   expr: "city"},
                          {name: "zip",    expr: "postalCode"}]}]}


{name: "us_core_blood_pressure",
 from: "Observation",
 select: [{name: "id", expr: "id"},
          {name: "patient_id", expr: "subject.getId()"} ,
          {name: "effective_date_time", expr: "effective.ofType(dateTime)"}
          {from: "component.where(code.coding.exists(system='http://loinc.org' and code='8480-6')).first()",
           select: [{name: "sbp_quantity_system",  expr: "value.ofType(Quantity).system"},
                    {name: "sbp_quantity_code",  expr: "value.ofType(Quantity).code"},
                    {name: "sbp_quantity_display",  expr: "value.ofType(Quantity).unit"},
                    {name: "sbp_quantity_value",  expr: "value.ofType(Quantity).value"}]},
          {from: "component.where(code.coding.exists(system='http://loinc.org' and code='8462-4')).first()",
           select: [{name: "dbp_quantity_system",  expr: "value.ofType(Quantity).system"},
                    {name: "dbp_quantity_code",  expr: "value.ofType(Quantity).code"},
                    {name: "dbp_quantity_display",  expr: "value.ofType(Quantity).unit"},
                    {name: "dbp_quantity_value",  expr: "value.ofType(Quantity).value"}]}]
 where: [{expr: "code.coding.exists(system='http://loinc.org' and code='85354-9')"},
           {expr: "component.where(code.coding.exists(system='http://loinc.org' and code='8480-6')).first().dataAbsentReason.empty()"},
           {expr: "component.where(code.coding.exists(system='http://loinc.org' and code='8462-4')).first().dataAbsentReason.empty()"}]}

[rbrush]

Thanks, Niquola! I went ahead and updated the original example using this convention, including your suggestion to replace "filter" with "where". This path does seem promising to me.

[rbrush]

An initial attempt at incorporating nested selects into the spec is at #78.

There are definitely open questions in this space so we can continue discussing prior to merge, or we can merge and update as we sort out more details.

[rbrush]

Closing this discussion since it has been merged into the spec. We can reopen others for any follow up items.