TDV Adapter for JSON

Build 22.0.8462

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 adapter 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.

agegenderfirst_namelast_namesourcetypemodelins_companyins_policy_nummaint_datemaint_desc
20MJohnDoeinternetcarHonda CivicABC Insurance123452017-07-17oil change
20MJohnDoeinternetcarHonda CivicABC Insurance123452018-01-03new tires
20MJohnDoeinternettruckDodge RamABC Insurance123452017-08-27new tires
20MJohnDoeinternettruckDodge RamABC Insurance123452018-01-08oil change
24FJaneRobertsphonecarToyota CamryCar Insurance987652017-05-11tires rotated
24FJaneRobertsphonecarToyota CamryCar Insurance987652017-11-03oil change
24FJaneRobertsphonecarHonda AccordCar Insurance987652017-10-07new air filter
24FJaneRobertsphonecarHonda AccordCar Insurance987652018-01-13new brakes

See Also

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462