Data mapping describes how elements are mapped between two distinct data models. To support SQL access to a REST service, the REST endpoint must be mapped to a relational schema. The driver automatically generates a relational view of your data the first time you execute a SQL statement. 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.
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.
The primary key for parent tables are determined heuristically from the top-level fields in the document (see "Determining the primary key"). For example, resident_id. If necessary, you can designate a new primary key in a parent table by editing the resolved REST file. For details, see "Designating a primary key."
You can specify the name of the parent table using the Table property. 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 Sample connection property, 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 property.
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.
Simple and nested objects are flattened and mapped to a parent table
