Introduction

A REST Datasource can present very straightforward data, such as a one-dimensional table and an obvious primary key. Then again, it might have implicit structure of data and types that need to be clearly defined to support SQL queries.

The mapping of the sample JSON document produced one parent table and one child table. In the parent table, the object procedureCode is viewed as a relational column. 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 PROCEDURECODE_RATE column contains the values of the rate objects that are nested in the rates object. The primary key is determined by the first field detected in the document, procedureCode.

The following snippet is the results String with a request URL that is filtering for just the procedureCode B5120ZZ:


When JSON formatting is applied, it is easier to see the data structure. Note that the results table corresponds to the top-level entities in the JSON and the rates table to the individual rates for procedures. The procedureCode is evaluated by the Autonomous REST Connector and discovered as unique, so it is set as the key. The JSON data is mapped into this schema:


But there are two effective date ranges and rates. There is an implicit association of 0 to n rates for each procedureCode. The Autonomous REST Connector creates synthetic key fields that are added to the rates table. When JSON is viewed as nodes, the two rates are distinguished by the integer incrementor that becomes the synthetic key field, as illustrated:


You are ensured a unique primary key (PK) by melding the incremental value with procedureCode for a rate – it links a row in the rate table to a row in the results table.

Note: The URL that is the target for the import of REST metadata must return JSON representing unique keys that will be mapped to your vocabulary. If it does not, the position field will be added to the generated schema to uniquely identify instances.
When JSON is viewed from a database point of view, the ability to distinguish the two rates is done with a synthetic integer incrementor, as illustrated:


Export a discovered schema

The Autonomous REST Connector schema discovery mechanism generates a schema for a REST service. You can make changes or add your preferred schema. Once you have fully defined a REST connection, it will be saved in the Vocabulary so it does not need to be recreated on each use. If you want to manually edit a REST schema to, for example, tweak a data type, click SCHEMA Export to export it to a text file. In that circumstance, you must specify the schema file when configuring a REST Datasource. Here is the exported schema from the REST sample:



Here is the subtly different exported schema from REST in the Mixed Connectivity sample:



Corticon does not provide any instructions on manipulation of schema files. See the Progress DataDirect Autonomous REST Connector for JDBC documentation and tooling for creation of schema files.

After you save your updated schema file, click SCHEMA Import to apply it.