Joining Object Arrays into a Single Table
With DataModel set to "FlattenedDocuments", nested documents will behave as separate tables and act in the same manner as a SQL JOIN. Any nested documents, at the same height (e.g. sibling documents), will be treated as a SQL CROSS JOIN.
Example
Below is a sample query and the results, based on the sample document in
Raw Data. This implicitly JOINs the insured document with the nested vehicles document.
Query
The following query drills into the nested documents in each insured document.
SELECT
"_id",
"name",
"address.street" AS address_street,
"address.city.first" AS address_city,
"address.state.last" AS address_state,
"insured_ages",
"year",
"make",
"model",
"body_style",
"_insured_id",
"_vehicles_c_id"
FROM
"insured"
Results
_id | name | address_street | address_city | address_state | insured_ages | year | make | model | body_style | _insured_id | _vehicles_c_id |
1 | John Smith | Main Street | Chapel Hill | NC | [ 17, 43, 45 ] | 2015 | Dodge | RAM 1500 | TK | 1 | 1 |
1 | John Smith | Main Street | Chapel Hill | NC | [ 17, 43, 45 ] | 2015 | Suzuki | V-Strom 650 XT | MC | 1 | 2 |
1 | John Smith | Main Street | Chapel Hill | NC | [ 17, 43, 45 ] | 1992 | Harley Davidson | FXR | MC | 1 | 3 |
2 | Joseph Newman | Oak Street | Raleigh | NC | [ 23, 25 ] | 2010 | Honda | Accord | SD | 2 | 4 |
2 | Joseph Newman | Oak Street | Raleigh | NC | [ 23, 25 ] | 2008 | Honda | Civic | CP | 2 | 5 |
See Also
• FreeForm;: Use dot notation to select nested data.
• VerticalFlattening;: Access nested object arrays as separate tables.
• JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.