Mapping nested complex types

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"}}
}

First, the EMPLOYEE parent table would be derived from the simple types _id and name as follows:

Second, the EMPLOYEE_MANAGER child table would be derived from the manager object. The id_ field is mapped as EMPLOYEE_ID. (This column establishes a foreign key relationship to the parent table and functions as a primary key within the child table.) In turn, the name and department subdocuments are mapped as columns. The resulting table would take the following form:

Third, an EMPLOYEE_MANAGER_LOCATION child table would be derived from the location object. The id_ field is mapped as EMPLOYEE_MANAGER_ID. (As in the previous table, this column establishes a foreign key relationship to the parent table and functions as a primary key within the child table.) The city and state subdocuments, which indicate the location of an employee's manager, are mapped as CITY and STATE columns. The resulting table would take the following form:

Subdocuments Nested in an Array

In the collection employee, the addresses array contains the subdocuments: label, street, city, and state.

{"_id": ajx456,
 "name": "Andrea",
 "addresses": [
               {"label": "Home", "street": "101 Main St", 
                "city": "Raleigh", "state": "NC"},
               {"label": "Work", "street": "303 Main St", 
                "city": "Morrisville","state": "NC"} 
              ]
}

First, the EMPLOYEE parent table is derived from the simple types _id and name as follows:

Second, the EMPLOYEE_ADDRESSES child table would be derived from the addresses array. In this scenario, the id_ field is mapped as EMPLOYEE_ID and retains a foreign key column relationship to the parent table. Next, each of the subdocuments in the array are mapped as columns. The driver also generates an additional column to establish a primary key for the child table: ADDRESSES_GENERATED_ID. This automatically generated column enables the driver to normalize nested arrays to any depth. The unique keys are internal to the driver. The resulting table takes the following form:

An Array Nested in a Document

In a separate 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", "cwright@email.com"]}
}

Again, the EMPLOYEE parent table would be derived from the simple types _id and name as follows:

Next, the EMPLOYEE_MANAGER child table would be derived from the manager object. As in previous examples, the id_ field is mapped as EMPLOYEE_ID and retains a foreign key relationship to the parent table. In turn, the nested name subdocument (the name of the manager) is mapped as a column. Next, the emails array is mapped to the column EMAILS. Each value in the EMAILS column corresponds to an element in the emails array. The driver also generates an additional column to establish a primary key for the child table: MANAGER_GENERATED_ID. Note that the names and emails in the following this child table are those of the managers but are linked to the employee identification number.

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"}
                               ]
                 }]
}

First, an 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.

Next, an OFFICES_DEPARTMENTS child table is derived from the departments array. The OFFICES_ID column is generated to establish a foreign key relationship to the parent table based on the _id field. The departments array is mapped to the column DEPARTMENTS. Each value in the DEPARTMENTS column corresponds to an element in the departments array. The DEPARTMENTS_GENERATED_ID column is generated to establish a primary key for the child table. (The unique keys are internal to the driver.)

The employees array nested in the departments array is mapped as an OFFICES_DEPARTMENTS_EMPLOYEES child table. (This table is the child of the OFFICES_DEPARTMENTS table and the grandchild of the OFFICES table.) The OFFICES_DEPARTMENTS_ID column establishes a foreign key relationship to the parent array based on the automatically generated primary key from the OFFICES_DEPARTMENTS table. The FIRST and LAST columns are derived from the the first and last fields contained in each employees array. The EMPLOYEES_GENERATED_ID column is generated to establish a primary key for the child table.