ODBC Driver for JSON

Build 22.0.8479

リレーショナルモデル

The CData ODBC Driver 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".

Connecting String

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.

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

クエリ

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

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

関連項目

  • 自動スキーマ検出:テーブルスキーマに報告されたカラムを設定します。
  • フリーフォームクエリ:ドット表記を使用して、ネストされたデータを選択します。
  • 垂直フラット化:ネストされたオブジェクト配列に別のテーブルとしてアクセスします。
  • JSON 関数:クライアント側の集計と変換を実行するために返されたデータを操作します。

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