In this section we will show how to control the various schemes that the add-in offers to bridge the gap with relational SQL and JSON services. The CData Excel Add-In for JSON 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 add-in 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 add-in models object arrays into tables. See Parsing Hierarchical Data for a guide.

Configuring Automatic Schema Discovery

The add-in 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 add-in enables you to query nested data with the following capabilities:

• Vertical Flattening: Access nested object arrays as separate tables.
• Free-Form Queries: Query any nested structure without flattening the data.
• JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

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 add-in'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.