When generating the relational view, the driver decompounds JSON documents returned by endpoints into parent-child tables. The driver handles mapping in the following manner:
  • Simple and nested objects are flattened and mapped to a parent table
  • Arrays of objects and arrays of strings are mapped to related child tables
  • If a JSON map is detected, it is normalized into a child table. See "Normalizing a JSON map" for a list of detectable map types and a description of normalizing JSON maps.

For example, the following JSON document contains nested objects in the address object, an array strings in the vehicles object, and an array of objects in the pets object. 

{"resident_id":"ajx363",
 "name":"Sydney Smith",
 "address":{"street": "101 Main Street", "city": "Raleigh", "state": "NC"},
 "county":"Wake",
 "pets":[{"species":"dog","breed":"beagle","weight":"35"}],
 "vehicles":["car","boat","bicycle"]
},
{"resident_id":"tzn525",
 "name":"Cora Welch",
 "address":{"street":"191 First Street","city":"Chapel Hill","state":"NC"},
 "county":"Orange",
 "pets":[{"species":"pig","breed":"yorkshire","weight":"55"}]
 "vehicles": ["scooter","truck","bicycle"]
}

When generating the relational view, the driver decompounds native objects into separate, but related tables. The mapping of the sample JSON document produced one parent table and two child tables. In the parent table, simple objects, such as name and county, are flattened into corresponding relational columns. Nested objects are also flattened into relational columns; however, column names are formed by concatenating the name of the parent and nested objects, which are joined by an underscore character. For example, the ADDRESS_STEET column contains the values of the street object that is nested in the address object.

Note: When an endpoint features a top-level object that contains only arrays, instead of mapping an empty table, the driver omits the object’s table from the relational view and promotes tables generated from the subordinate arrays to the top level. The driver’s log records empty tables that are excluded from the relational view using the following message: Empty table is not being persisted: table_name.

Primary keys for parent tables are determined heuristically from the top-level fields in the document. For example, resident_id. However, if none of the fields are determined to be viable candidates, the driver generates a primary key column, ROWID. Be aware that, when a ROWID column is generated, the driver also flattens and maps objects into a single table. See "Determining the primary key" for more information. Note that you can designate a new primary key in a parent table using the Configuration Manager. For details, see "Customizing your Schema."

You can specify the name of the parent table using the Table (Table) connection option. If no value is specified, The name is derived from the endpoint from which the data was sampled. For example, for the endpoint https://example.com/residents/2, the table would be named residents_2 by default.
Note: When using the REST Sample Path (Sample) connection option, the driver maps endpoints that consist of only a host name to the URL_ parent table by default. You can specify a different table name using the Table option.
Note: If a naming conflict occurs, a suffix comprised of an underscore and numeral, starting at 1, is appended to the relational name of an object. For example, if your table contains an object that would normally map to POSITION, your object would map column POSITION_1 to avoid a conflict with the column used for composite keys.

The parent table for our example is named RESIDENTS_2 and takes the following form:

Table 1. RESIDENTS_2
RESIDENT_ID (PK) NAME ADDRESS_STREET ADDRESS_CITY ADDRESS_STATE COUNTY
ajx363 Sydney Smith 101 MAIN STREET Raleigh NC Wake
tzn525 Cora Welch 191 FIRST STREET Chapel Hill NC Orange

The data for the pets arrays of objects normalizes to PETS child tables. When discovered, the objects within an array are mapped to corresponding relational columns. For example, the species and breed array values from the pets array in the JSON sample, are mapped as columns to the following PETS table. A foreign key relationship to the parent table is provided by including the primary key of the parent in the child, in this case, RESIDENT_ID. The primary key of the child table is a composite key formed by the primary key of the parent table combined with the positional information contained in the POSITION column. If the array is nested multiple layers deep, additional positional columns for parent objects are mapped to insure that a unique key is used.

The child table for the pets array would take the following form:

Table 2. PETS
RESIDENTS_RESIDENT_ID (PK) POSITION (PK) SPECIES BREED WEIGHT
ajx363 0 dog beagle 35
tzn525 0 pig yorkshire 55

The information in the vehicles array of strings normalizes to the VEHICLES child table. The values of the array are mapped into a single relational column that corresponds to the name of the array. For example, the values for the vehicles array in the JSON sample, such as car and boat, map to the VEHICLES column in the VEHICLES table. To maintain a unique foreign key, the driver generates a POSITION common to differentiate from the duplicate primary keys derived from the parent table.

Table 3. VEHICLES
RESIDENTS_RESIDENT_ID (PK) POSITION (PK) VEHICLES
ajx363 0 car
ajx363 1 boat
ajx363 2 bicycle
tzn525 0 scooter
tzn525 1 truck
tzn525 2 bicycle