The CData ADO.NET Provider for REST can be configured to create a relational model of the data, treating each XPath as an individual table 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 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 file or source will be queried once for each table included in the query.
Below is a sample query against the sample document in Raw Data, using a relational model based on the XPaths "/root/people", "/root/people/vehicles", and "/root/people/vehicles/maintenance".
Set the DataModel connection property to "Relational" and set the XPath connection property to "/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;" to perform the following query and see the example result set.
The following query explicitly JOINs the people, vehicles, and maintenance tables.
SELECT [people].[personal.age] AS age, [people].[personal.gender] AS gender, [people].[personal.name.first] AS first_name, [people].[personal.name.last] AS last_name, [people].[source], [vehicles].[type], [vehicles].[model], [vehicles].[insurance.company] AS ins_company, [vehicles].[insurance.policy_num] AS ins_policy_num, [maintenance].[date] AS maint_date, [maintenance].[desc] AS maint_desc FROM [people] JOIN [vehicles] ON [people].[_id] = [vehicles].[people_id] JOIN [maintenance] ON [vehicles].[_id] = [maintenance].[vehicles_id]
In the example query, each maintenance element is JOINed to its parent vehicle element, which is JOINed to its parent people element to produce a table with 8 rows (2 maintenance entries for each of 2 vehicles each for 2 people).
|20||M||John||Doe||internet||car||Honda Civic||ABC Insurance||12345||2017-07-17||oil change|
|20||M||John||Doe||internet||car||Honda Civic||ABC Insurance||12345||2018-01-03||new tires|
|20||M||John||Doe||internet||truck||Dodge Ram||ABC Insurance||12345||2017-08-27||new tires|
|20||M||John||Doe||internet||truck||Dodge Ram||ABC Insurance||12345||2018-01-08||oil change|
|24||F||Jane||Roberts||phone||car||Toyota Camry||Car Insurance||98765||2017-05-11||tires rotated|
|24||F||Jane||Roberts||phone||car||Toyota Camry||Car Insurance||98765||2017-11-03||oil change|
|24||F||Jane||Roberts||phone||car||Honda Accord||Car Insurance||98765||2017-10-07||new air filter|
|24||F||Jane||Roberts||phone||car||Honda Accord||Car Insurance||98765||2018-01-13||new brakes|
- Automatic Schema Discovery: Configure the columns reported in the table schemas.
- Free-Form Queries: Use dot notation to select nested data.
- Vertical Flattening: Query nested data as separate tables.
- XML Functions and JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.