The CData Cmdlets PowerShell Module for Elasticsearch can be configured to create a relational model of the data, treating nested documents as individual tables containing a primary key and a foreign key that links to the parent document. This is particularly useful if you need to work with your Elasticsearch data in existing BI, reporting, and ETL tools that expect a relational data model.
Joining Nested Arrays as Tables
With DataModel set to "Relational", any JOINs are controlled by the query. Any time you perform a JOIN query, the Elasticsearch index will be queried once for each table (nested document) included in the query.
Below is a sample query against the sample document in 生データ, using a relational model.
The following query explicitly JOINs the insured and vehiclestables.
SELECT [insured].[_id], [insured].[name], [insured].[address.street] AS address_street, [insured].[address.city.first] AS address_city, [insured].[address.state.last] AS address_state, [insured].[insured_ages], [vehicles].[year], [vehicles].[make], [vehicles].[model], [vehicles].[body_style], [vehicles].[_insured_id], [vehicles].[_c_id] FROM [insured] JOIN [vehicles] ON [insured].[_id] = [vehicles].[_insured_id]
In the example query, each vehicle document is JOINed to its parent insured object to produce a table with 5 rows.
|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|