skip to main content
Welcome to the Progress DataDirect for JDBC for MongoDB Driver : Mapping Objects to Tables : Mapping Nested Complex Types
  

Try DataDirect Drivers Now

Mapping Nested Complex Types

The following examples illustrate several ways the normalization of complex types are handled.

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:
_ID
NAME
pdn313
Charlotte
gkx136
Benjamin
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:
EMPLOYEE_ID (PK & FK)
NAME
DEPARTMENT
pdn313
Robert
Development
gkx136
Michael
Quality Assurance
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:
EMPLOYEE_MANAGER_ID (PK & FK)
CITY
STATE
pdn313
Tulsa
OK
gkx136
Dallas
TX

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:
_ID
NAME
ajx456
Andrea
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:
EMPLOYEE_ID (FK)
LABEL
STREET
CITY
STATE
ADDRESSES_GENERATED_ID (PK)
ajx456
Home
101 Main St
Raleigh
NC
unique key
ajx456
Work
303 Main St
Morrisville
NC
unique key

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:
_ID
NAME
ajp211
Mark
mpc393
Deborah
dlm215
Jason
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.
EMPLOYEE_ID (FK)
NAME
EMAILS
MANAGER_GENERATED_ID (PK)
ajp211
Cynthia
cynthia@email.com
unique key
ajp211
Cynthia
watsonc@email.com
unique key
mpc393
Cynthia
cynthia@email.com
unique key
mpc393
Cynthia
watsonc@email.com
unique key
dlm215
Chris
chris@email.com
unique key
dlm215
Chris
cwright@email.com
unique key

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.
_ID (PK)
CITY
au32
Bedford
xn44
Morrisville
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.)
OFFICES_ID (FK)
DEPARTMENTS
DEPARTMENTS_GENERATED_ID (PK)
au32
Development
unique key
au32
Human Resources
unique key
au32
IT
unique key
xn44
Development
unique key
xn44
Operations
unique key
xn44
IT
unique key
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 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.
OFFICES_DEPARTMENTS_ID (FK)
FIRST
LAST
EMPLOYEES_GENERATED_ID (PK)
auto-generated key from the parent array
Leslie
Jacobs
unique key
auto-generated key from the parent array
Emma
Alves
unique key
auto-generated key from the parent array
Brad
Jones
unique key
auto-generated key from the parent array
Joseph
Lu
unique key
auto-generated key from the parent array
Margaret
Baker
unique key
auto-generated key from the parent array
Chetna
Campbell
unique key
auto-generated key from the parent array
Caroline
Evans
unique key
auto-generated key from the parent array
Markus
Campanella
unique key
auto-generated key from the parent array
Jennifer
Bradley
unique key
auto-generated key from the parent array
Charles
Scott
unique key
auto-generated key from the parent array
Mary
Gonzales
unique key
auto-generated key from the parent array
Phil
McEnroe
unique key
auto-generated key from the parent array
Rachel
Cullingford
unique key
auto-generated key from the parent array
Lance
Friedman
unique key
auto-generated key from the parent array
Amanda
Giachetti
unique key
auto-generated key from the parent array
Ingrid
Burkis
unique key
auto-generated key from the parent array
Catherine
Wheeler
unique key
auto-generated key from the parent array
Jacob
Williams
unique key