Nested complex types (normalized view)

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 in the employee collection would be mapped to the EMPLOYEE parent table, and the EMPLOYEE_MANAGER and the EMPLOYEE_LOCATION child tables. The EMPLOYEE table would be derived from the simple types _id and name as follows:

The information from the manager object is mapped to the following child table:

• The table name, EMPLOYEE_MANAGER, is derived from the name of the parent table and the object.
• 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 nested subdocuments are mapped to columns in the child table. For example, the name field is mapped to the NAME column.

The information in the location object is mapped to the following child table:

• The table name, EMPLOYEE_LOCATION, is derived from the name of the parent table and the object.
• The id_ field is mapped as EMPLOYEE_ID to establish a foreign key relationship to the parent table.
• Nested subdocuments are mapped to columns. In this example, the city and state subdocuments, which indicate the location of an employee's manager, are mapped as CITY and STATE columns.

Subdocuments nested in an array

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

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

The information in the contacts collection would be mapped to the CONTACT parent table, and the CONTACTS_ADDRESSES child table. The CONTACTS table would be derived from the simple types _id and name as follows:

The information in the addresses array maps to the following child table:

• The table name, CONTACTS_ADDRESSES, is derived from the name of the parent table and 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.
• Subdocuments in an array are mapped as columns. For example, the label field is mapped to the LABEL column.

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

The information in the employee collection would be mapped to the EMPLOYEE parent table, and the EMPLOYEE_EMAILS and EMPLOYEE_MANAGER child tables. The EMPLOYEE table would be derived from the simple types _id and name as follows:

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

• The table name, EMPLOYEE_EMAILS, is derived from the name of the parent table and 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, EMPLOYEE_ID.

• The primary key of the child table is a composite key formed by the primary key of the parent table (EMPLOYEE_ID) combined with the positional information contained in the POSITION column.
• Nested arrays are mapped as columns. For example, the emails array is mapped to the EMAILS column. Note that the emails in the following child table are those of the managers, but correspond 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"}
                               ]
                 }]
}

The information in the offices collection would be mapped to the OFFICES parent table, and the OFFICES_DEPARTMENTS and OFFICES_EMPLOYEES child tables. The OFFICES table would be derived from the simple types _id and city as follows:

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

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

• The table name, OFFICES_EMPLOYEES, is derived from the name of the parent table and the array.
• The OFFICES_ID and OFFICES_DEPARTMENTS_POSITION columns establish a foreign key relationship to the parent array based on the primary key from the DEPARTMENTS table.
• 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.
• Nested arrays are mapped as columns. For example, the first array is mapped to the FIRST column.