# SQL on FHIR - View layer test cases

## Test case 1

Select ID, gender and birth date for each patient.

Tests the use of singular elements with no unnesting.

### Input

{
  "resource": "Patient",
  "columns": [
    {
      "name": "id",
      "expr": "id"
    },
    {
      "name": "gender",
      "expr": "gender"
    },
    {
      "name": "birth_date",
      "expr": "birthDate"
    }
  ]
}

### Expected result

| id | gender | birth_date |
|----|--------|------------|
| 1 | female | 1959-09-27 |
| 2 | male | 1983-09-06 |

## Test case 2

Select ID, name use and family name for each patient.

Tests the unnesting of two elements that are singular relative to their common repeating parent.

### Input

{
  "resource": "Patient",
  "vars": [
    {
      "name": "name",
      "expr": "name",
      "whenMany": "unnest"
    }
  ],
  "columns": [
    {
      "name": "id",
      "expr": "id"
    },
    {
      "name": "name_use",
      "expr": "%name.use"
    },
    {
      "name": "family_name",
      "expr": "%name.family"
    }
  ]
}

### Expected result

| id | name_use | family_name |
|----|----------|-------------|
| 1 | maiden | Wuckert |
| 1 | official | Oberbrunner |
| 2 | nickname | Cleveland |
| 2 | official | Towne |

## Test case 3

Select ID, family name and given name for each patient.

Tests multiple levels of unnesting that share the same lineage.

### Input

{
  "resource": "Patient",
  "vars": [
    {
      "name": "name",
      "expr": "name",
      "whenMany": "unnest"
    },
    {
      "name": "givenName",
      "expr": "name.given",
      "whenMany": "unnest"
    }
  ],
  "columns": [
    {
      "name": "id",
      "expr": "id"
    },
    {
      "name": "family_name",
      "expr": "%name.family"
    },
    {
      "name": "given_name",
      "expr": "%givenName"
    }
  ]
}

### Expected result

| id | family_name | given_name |
|----|-------------|------------|
| 1 | Wuckert | Karina |
| 1 | Oberbrunner | Karina |
| 2 | Towne | Guy |
| 2 | Cleveland | Maponos |
| 2 | Cleveland | Wilburg |

## Test case 4

Select ID, name prefix, family name, marital status system and marital status
code for each patient.

Tests two sets of multi-level nesting that have the root resource as their nearest common ancestor.

:::warning
This doesn't match the current draft spec, which states that this should throw an error unless a `whenMany` value of `cross` is used. This is an item for discussion.
:::

### Input

{
  "resource": "Patient",
  "vars": [
    {
      "name": "name",
      "expr": "name",
      "whenMany": "unnest"
    },
    {
      "name": "namePrefix",
      "expr": "name.prefix",
      "whenMany": "unnest"
    },
    {
      "name": "maritalStatus",
      "expr": "maritalStatus.coding",
      "whenMany": "unnest"
    }
  ],
  "columns": [
    {
      "name": "id",
      "expr": "id"
    },
    {
      "name": "name_prefix",
      "expr": "%namePrefix"
    },
    {
      "name": "family_name",
      "expr": "%name.family"
    },
    {
      "name": "marital_status_system",
      "expr": "%maritalStatus.system"
    },
    {
      "name": "marital_status_code",
      "expr": "%maritalStatus.code"
    }
  ]
}

### Expected result

| id | name_prefix | family_name | marital_status_system | marital_status_code |
|----|-------------|-------------|--------------------------------------------------------|---------------------|
| 1 | Miss. | Wuckert | http://terminology.hl7.org/CodeSystem/v3-MaritalStatus | M |
| 1 | Miss. | Wuckert | http://snomed.info/sct | 87915002 |
| 1 | Mrs. | Oberbrunner | http://terminology.hl7.org/CodeSystem/v3-MaritalStatus | M |
| 1 | Mrs. | Oberbrunner | http://snomed.info/sct | 87915002 |
| 2 | Mr. | Towne | NULL | NULL |
| 2 | Prof. | Cleveland | NULL | NULL |

## Test case 5

Select ID, given name and family name for each patient.

Tests the ability to return columns as arrays, where the expressions that define those columns return a collection.

### Input

{
  "resource": "Patient",
  "vars": [
    {
      "name": "givenName",
      "expr": "name.given",
      "whenMany": "array"
    }
  ],
  "columns": [
    {
      "name": "id",
      "expr": "id"
    },
    {
      "name": "given_name",
      "expr": "%givenName"
    }
  ]
}

### Expected result

| id | given_name |
|----|-------------------------|
| 1 | [Karina, Karina] |
| 2 | [Guy, Maponos, Wilburg] |

## Test case 6

Select ID and given name for each patient, with a row for each name and the given names for each name within an array.

Tests the ability to use the unnest and array directives together.

### Input

{
  "resource": "Patient",
  "vars": [
    {
      "name": "name",
      "expr": "name",
      "whenMany": "unnest"
    },
    {
      "name": "givenName",
      "expr": "%name.given",
      "whenMany": "array"
    }
  ],
  "columns": [
    {
      "name": "id",
      "expr": "id"
    },
    {
      "name": "given_name",
      "expr": "%givenName"
    }
  ]
}

### Expected result

| id | given_name |
|----|--------------------|
| 1 | [Karina] |
| 1 | [Karina] |
| 2 | [Guy] |
| 2 | [Maponos, Wilburg] |

## Test case 7

Select ID and given name for each patient, with the given names for each name represented within a nested array.

Tests the ability to use the multiple array directives together.

### Input

{
  "resource": "Patient",
  "vars": [
    {
     
      "name": "name",
      "expr": "name",
      "whenMany": "array"
    },
    {
      "name": "givenName",
      "expr": "%name.given",
      "whenMany": "array"
    }
  ],
  "columns": [
    {
      "name": "id",
      "expr": "id"
    },
    {
      "name": "given_name",
      "expr": "%givenName"
    }
  ]
}

### Expected result

| id | given_name |
|----|-----------------------------|
| 1 | [[Karina], [Karina]] |
| 2 | [[Guy], [Maponos, Wilburg]] |

## Test case 8

Select ID and family name for each patient, but force an error through the use of the `error` value for `whenMany`.

This tests the behaviour of the `error` value for `whenMany`.

## Input

{
  "resource": "Patient",
  "vars": [
    {
     
      "name": "name",
      "expr": "name",
      "whenMany": "error"
    }
  ],
  "columns": [
    {
      "name": "id",
      "expr": "id"
    },
    {
      "name": "family_name",
      "expr": "%name.family"
    }
  ]
}

## Expected result

An error stating that the `name` variable resulted in a collection and the `whenMany` directive was specified as `error`.

## Test data

{
  "resourceType": "Patient",
  "id": "1",
  "gender": "female",
  "birthDate": "1959-09-27",
  "name": [
    {
      "use": "official",
      "family": "Oberbrunner",
      "given": [
        "Karina"
      ],
      "prefix": [
        "Mrs."
      ]
    },
    {
      "use": "maiden",
      "family": "Wuckert",
      "given": [
        "Karina"
      ],
      "prefix": [
        "Miss."
      ]
    }
  ],
  "maritalStatus": {
    "coding": [
      {
        "system": "http://terminology.hl7.org/CodeSystem/v3-MaritalStatus",
        "code": "M",
        "display": "M"
      },
      {
        "system": "http://snomed.info/sct",
        "code": "87915002",
        "display": "Married"
      }
    ],
    "text": "Married"
  }
}

{
  "resourceType": "Patient",
  "id": "2",
  "gender": "male",
  "birthDate": "1983-09-06",
  "name": [
    {
      "use": "official",
      "family": "Towne",
      "given": [
        "Guy"
      ],
      "prefix": [
        "Mr."
      ]
    },
    {
      "use": "nickname",
      "family": "Cleveland",
      "given": [
        "Maponos",
        "Wilburg"
      ],
      "prefix": [
        "Prof."
      ]
    }
  ]
}