TDV Adapter for JSON

Build 22.0.8462

Modeling JSON Data

In this section we will show how to control the various schemes that the adapter offers to bridge the gap with relational SQL and JSON services. The JSON Adapter provides a managed way for you to use the two prevailing techniques for dealing with nested JSON data:

  • Parsing the data structure and building a relational model based on the existing hierarchy.
  • Drilling down into the nested arrays and objects using horizontal and vertical flattening.

Parsing Hierarchical Data

By default, the adapter automatically detects the rows in a document, so that you do not need to know the structure of the underlying data to query it with SQL. Set the DataModel property to choose a basic configuration of how the adapter models object arrays into tables. See Parsing Hierarchical Data for a guide.

Configuring Automatic Schema Discovery

The adapter automatically discovers columns and data types by scanning a configured number of objects in JSON arrays. Set the FlattenObjects and FlattenArrays properties to configure how nested data is flattened into columns; see Automatic Schema Discovery for examples.

Executing SQL to JSON

Any relation you can access through flattening you can also access with an ad-hoc SQL query. The adapter enables you to query nested data with the following capabilities:

Customizing Schemas

Customizing Schemas enables you to project your chosen relational structure on top of a JSON document. This allows you to choose the names of columns, their data types, and the locations of their values in the collection.

System Catalog

The System Tables reflect the schemas you configured, custom schemas or dynamically discovered. The Stored Procedures surface additional functionality in the adapter's data processing operations that cannot be modeled as SELECT, INSERT, UPDATE, or DELETE. You can find the reported stored procedures defined in .api files in the folder specified by Location -- if Location is not specified, the db subfolder of the installation directory.

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