Flattened Documents Model

For users who simply need access to the entirety of their JSON data, flattening the data into a single table is the best option. The add-in will use streaming and only parses the JSON data once per query in this mode.

Joining Object Arrays into a Single Table

With DataModel set to "FlattenedDocuments", nested JSONPath values will act in the same manner as a SQL JOIN. Any nested sibling JSONPath values (child paths at the same height) 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 and parsing based on the JSON paths $.people, $.people.vehicles, and $.people.vehicles.maintenance. This implicitly JOINs the people collection with the vehicles collection and implicitly JOINs the vehicles collection with the maintenance collection.

Connection String

Use the following connection string to query the Raw Data in this example.

URI=C:\people.txt;DataModel=FlattenedDocuments;JSONPath='$.people;$.people.vehicles;$.people.vehicles.maintenance;'

Query

The following query drills into the nested elements in each people object. Since the JSONPath property included the vehicles collection as a JSON path, you can query an element of a vehicle explicitly.

SELECT
  [personal.age] AS age,
  [personal.gender] AS gender,
  [personal.name.first] AS name_first,
  [personal.name.last] AS name_last,
  [source],
  [type],
  [model],
  [insurance.company] AS ins_company,
  [insurance.policy_num] AS ins_policy_num,
  [date] AS maint_date,
  [desc] AS maint_desc
FROM
  [people]

Results

With horizontal and vertical flattening based on the described paths, each vehicle object is implicitly JOINed to its parent people object and each maintenance object is implicitly JOINed to its parent vehicle object.


age	gender	first_name	last_name	source	type	model	ins_company	ins_policy_num	maint_date	maint_desc
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