Flattened Documents Model
For users who need access to the entirety of their nested Elasticsearch data, flattening the data into a single table is the best option. The add-in will use streaming and only parses the Elasticsearch data once per query in this mode.
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
- Automatic Schema Discovery: Configure the columns reported in the table schemas.
- 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.