The following examples illustrate a number of ways the driver maps complex types in a mixed representation of the relational schema.

A subdocument nested in a subdocument

In this example, the subdocument location contains the subdocuments city and state in the employee collection:

{"_id": "pdn313",
 "name": "Charlotte",
 "manager": {"name": "Robert", "department": "Development",
 "location": {"city": "Tulsa", "state": "OK"}}
}

{"_id": "gkx136",
 "name": "Benjamin",
 "manager": {"name": "Michael", "department": "Quality Assurance",
             "location": {"city": "Dallas", "state": "TX"}}
}

The information from the employee object is mapped to the following table:

Table 1. EMPLOYEE
_ID NAME MANAGER_ NAME MANAGER_ DEPARTMENT MANAGER_ LOCATION_CITY MANAGER_ LOCATION_STATE
pdn313 Charlotte Robert Development Tulsa OK
gkx136 Benjamin Michael Quality Assurance Dallas TX

The mapping of the employee collection to the EMPLOYEE table is handled as follows:

  • Simple types are mapped to columns in the parent table as columns. For example, the name object is mapped to NAMES column.
  • Subdocuments nested in subdocuments are mapped as columns to the parent table using the following naming convention: 
    <subdocument_name>_<nested_subdocument_name>_<field_name>

    For example, for the location subdocument nested in the manager subdocument, the city field would map to a column name of MANAGER_LOCATION_CITY.

Subdocuments nested in an array

In the collection contacts, the addresses array contains the subdocuments: label, street, and zip.

{"_id": "ajx456",
 "name": "Andrea",
 "addresses": [
			 {"label": "Home", "street": "101 Main St", "zip": "27513"},
			 {"label": "Work", "street": "303 Main St", "zip": "27560"} 
             ]
}

In this example, the information from the contacts collection is mapped to the CONTACTS parent table and CONTACTS_ADDRESSES child table. The CONTACTS parent table is derived from the _id and name simple types, which are mapped to the _ID and NAMES column. The resulting parent table would take the following form:

Table 2. CONTACTS
_ID NAME
ajx456 Andrea
The information from the ADDRESSES array is mapped to the following child table:
Table 3. CONTACTS_ADDRESSSES
CONTACTS_ID (FK) POSITION (PK) LABEL STREET ZIP
ajx456 0 Home 101 Main St 27513
ajx456 1 Work 303 Main St 27560
The mapping of the parent array, addresses, to the CONTACTS_ADDRESSES table is handled as follows:
  • The table name, CONTACTS_ADDRESSES, is derived from the name of the array.
  • To establish a foreign key relationship with the parent table, a column is generated based on the _ID field using the following naming convention: 
    <parent_table>_ID

    For example, CONTACTS_ID.

  • The primary key of the child table is a composite key formed by the primary key of the parent table (CONTACTS_ID) combined with the positional information contained in the POSITION column.
  • Fields in the array are mapped to columns in the child table. For example, the label field becomes the LABEL column.

An array nested in a document

In this scenario, the manager document contains the emails array in the employee collection. The collection has the following JSON structure:

{"_id": "ajp211",
 "name": "Mark",
 "manager": {"name": "Cynthia",
             "emails": ["cynthia@email.com", "watsonc@email.com"]}
}
{"_id": "mpc393",
 "name": "Deborah",
 "manager": {"name": "Cynthia",
             "emails": ["cynthia@email.com", "watsonc@email.com"]}
}
{"_id": "dlm215",
 "name": "Jason",
 "manager": {"name": "Chris",
             "emails": ["chris@email.com"]}
}
The information from the employee collection is mapped to the EMPLOYEE parent table and the EMPLOYEE_EMAILS child table.
Important: In the mixed view, if the number of values in an array are uniform across the collection and are less than or equal to twelve per element, the array values are flattened into columns in the parent table (for example, EMAILS_1, EMAILS_2, EMAILS_3). If the number of values are not uniform or exceed twelve per element, the driver maps the array to a separate child table, as demonstrated by this example.
The resulting parent table takes the following form:
Table 4. EMPLOYEE
_ID NAME MANAGER_NAME
ajp211 Mark Cynthia
mpc393 Deborah Cynthia
dlm215 Jason Chris
Mapping for the EMPLOYEE parent table is handled as follows:
  • Simple types are mapped to columns in the parent table as columns. For example, the name object is mapped to NAME column.
  • Fields for the subdocument manager are mapped as columns to the parent table. Column names for fields generated from a subdocument take the following form: 
    <subdocument_name>_<field_name>

    For example, for the field name in the subdocument manager, the resulting column name would be MANAGER_NAME.

The information from the emails nested array is mapped to the following child table:

Table 5. EMPLOYEE_EMAILS
EMPLOYEE_ID (FK) POSITION (PK) EMAILS
ajp211 0 cynthia@email.com
ajp211 1 watsonc@email.com
mpc393 0 cynthia@email.com
mpc393 1 watsonc@email.com
dlm215 0 chris@email.com
The mapping of the emails array to the EMPLOYEE_EMAILS table is handled as follows:
  • The table name is now derived from the name of the array. For example, EMAILS.
  • To establish a foreign key relationship with the parent table, a column is generated based on the _ID field using the following naming convention: 
    <parent_table>_ID

    For example, EMPLOYEE_ID.

  • The primary key of the child table is a composite key formed by the primary key of the parent table (MANAGER_ID) combined with the positional information contained in the POSITION column.
  • Nested arrays are mapped to the column. In this example, the emails array is mapped to the EMAILS column.

An array nested in an array

In the collection offices, the departments array contains an employees array.

{"_id": "au32",
 "city": "Bedford",
 "departments": [{"name": "Development",
                  "employees": [
                                {"first": "Leslie", "last": "Jacobs"},
                                {"first": "Emma", "last": "Alves"},
                                {"first": "Brad", "last": "Jones"}
                               ]
                  },
                  {"name": "Human Resources",
                   "employees": [
                                 {"first": "Joseph", "last": "Lu"},
                                 {"first": "Margaret", "last": "Baker"},
                                 {"first": "Chetna", "last": "Campbell"}
                                ]
                  },
                  {"name": "IT",
                   "employees": [
                                 {"first": "Caroline", "last": "Evans"},
                                 {"first": "Markus", "last": "Campanella"},
                                 {"first": "Jennifer", "last": "Bradley"}
                                 ]
                  }]
}
{"_id": "xn44",
 "city": "Morrisville",
 "departments": [{"name": "Development",
                  "employees": [
                                {"first": "Charles", "last": "Scott"},
                                {"first": "Mary", "last": "Gonzales"},
                                {"first": "Phil", "last": "McEnroe"}
                               ]
                 },
                 {"name": "Operations",
                  "employees": [
                                {"first": "Rachel", "last": "Cullingford"},
                                {"first": "Lance", "last": "Friedman"},
                                {"first": "Amanda", "last": "Giachetti"}
                               ]
                 },
                 {"name": "IT",
                  "employees": [
                                {"first": "Ingrid", "last": "Burkis"},
                                {"first": "Catherine", "last": "Wheeler"},
                                {"first": "Jacob", "last": "Williams"}
                               ]
                  }]
}

The information from the offices collection is mapped to the OFFICES parent table and the OFFICES_DEPARTMENTS and OFFICES_EMPLOYEES child tables. The OFFICES parent table is derived from the _id and city simple types. The _id field is mapped as the primary key column _ID, and the city field is mapped as the relational column CITY. The resulting OFFICES parent table takes the following form:

Table 6. OFFICES
_ID (PK) CITY
au32 Bedford
xn44 Morrisville

The information in the departments parent array is mapped to the following child table:

Table 7. OFFICES_DEPARTMENTS
OFFICES_ID (FK) POSITION (PK) NAME
au32 0 Development
au32 1 Human Resources
au32 2 IT
xn44 0 Development
xn44 1 Operations
xn44 2 IT
The mapping of the departments parent array to the OFFICES_DEPARTMENTS child table is handled as follows:
  • The table name is now derived from the name of the parent table and the array. For example, OFFICES_DEPARTMENTS.
  • To establish a foreign key relationship with the parent table, a column is generated based on the _ID field using the following naming convention: 
    <parent_table>_ID

    For example, OFFICES_ID.

  • The primary key of the child table is a composite key formed by the primary key of the parent table (OFFICES_ID) combined with the positional information contained in the POSITION column.
  • The fields in the parent array are mapped to columns. For example, the name array is mapped to the NAME column.

The information in the employees nested array is mapped to the following child table:

Table 8. OFFICES_EMPLOYEES
OFFICES_ID (FK) OFFICES_DEPARTMENTS _POSITION POSITION (PK) FIRST LAST
au32 0 0 Leslie Jacobs
au32 0 1 Emma Alves
au32 0 2 Brad Jones
au32 1 0 Joseph Lu
au32 1 1 Margaret Baker
au32 1 2 Chetna Campbell
au32 2 0 Caroline Evans
au32 2 1 Markus Campanella
au32 2 2 Jennifer Bradley
xn44 0 0 Charles Scott
xn44 0 1 Mary Gonzales
xn44 0 2 Phil McEnroe
xn44 1 0 Rachel Cullingford
xn44 1 1 Lance Friedman
xn44 1 2 Amanda Giachetti
xn44 2 0 Ingrid Burkis
xn44 2 1 Catherine Wheeler
xn44 2 2 Jacob Williams
The mapping of the departments array to the OFFICES_DEPARTMENTS child table is handled as follows:
  • The table name is derived from the name of the parent table and the array. For example, OFFICES_EMPLOYEES.
  • The foreign key, OFFICES_ID, reflects the name of the parent table.
  • A foreign key relationship with the parent array is established using the <parent_array>_POSITION column. For example, OFFICES_DEPARTMENTS_POSITION.
  • The primary key of the child table is a composite key formed by the primary key of the parent (OFFICES_ID), the positional information of the parent of the array (OFFICES_DEPARTMENTS_POSITION), and the positional information contained in the POSITION column.
  • The fields in the nested array are mapped to columns in the table. For example, the first array is mapped to the FIRST column.