---
# System prepended metadata

title: SQL on FHIR - View layer test cases

---

# 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

```json
{
  "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

```json
{
  "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

```json
{
  "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

```json
{
  "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

```json
{
  "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

```json
{
  "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

```json
{
  "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

```json
{
  "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

```json
{
  "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"
  }
}
```

```json
{
  "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."
      ]
    }
  ]
}
```
