The JSON data returned from a REST datasource can be—and often is—hierarchical. There are some special concerns when mapping this hierarchical data in Studio.

In order to properly map associations (which are essentially joins between 2 tables), the Entity Identity must be set for the Entity to specify which attributes are part of the primary key. Additionally, the join expression for the association must be set to define which attributes are used to create the join.

The REST Services driver automatically creates the primary key for the tables. In most cases the primary key is the first field in the JSON for that object, unless there is some reason that it cannot be used as a primary key, or a different field was determined to be a better fit. When the data contains arrays of objects, the Autonomous REST Connector creates additional fields named "POSITION", and then adds them to the primary key. This field indicates where the object was found in the array.

If parameters are also mapped, then the parameter column is also added to the primary key, so these columns must also have corresponding attributes in the Entity.

In summary, the primary key or Entity Identity for an Entity will consist of:

  • At least one attribute that was determined to be unique across all of the elements of the array
  • For arrays, a position field to specify the location in the array, if a unique field could not be identified.
  • Any URL parameter columns

For associated Entities there will be additional elements in the primary key or Entity Identity :

  • The ID of the root element that this element has as a parent
  • The position field of the parent object (if it was determined to be in an array
  • The position field of the parent's parent recursively back up to the root table (REST_DATA).
For example, if we have the following structure:
Vehicle
Id
→Devices
Id
→ Radios
Id

The Autonomous REST Connector would create this structure:

Vehicle
*Id
→Devices
*position
*VehicleID
Id
→ Radios
*DevicesPosition
*position
*VehiclePosition
Id

The more nested the structure, the more complex the primary keys get for the lower levels, because the keys from the previous levels have to be maintained at each level.

The potentially confusing point is that the position columns are added by the Autonomous REST Connector and will have to have corresponding attributes in the vocabulary. These attributes do not exist in the JSON document and will need to be manually added during modeling (they could be set to transient if you prefer).

When there is a name conflict, or a conflict with a reserved word, Autonomous REST Connector will post-pend an underscore character or an underscore with an integer. This can make mapping difficult, as it could be hard to distinguish them.