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:

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