The CData Power BI Connector for JSON can be configured to create a relational model of the data, treating nested object arrays 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 JSON 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 JSON file or source will be queried once for each table (nested array) included in the query.
Below is a sample query against the sample document in Raw データ, using a relational model based on the JSON paths "$.people", "$.people.vehicles", and "$.people.vehicles.maintenance".
Set the DataModel connection property to "Relational" and set the JSONPath connection property to "$.people;$.people.vehicles;$.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 object is JOINed to its parent vehicle object, which is JOINed to its parent people object 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|