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 structure that the Autonomous REST Connector would create would be this:

Vehicle

*Id

→Devices

*position

*VehicleID

Id

→ Radios

*DevicesPosition

*position

*VehiclePosition

Id

As you can see, the more nested the structure, the more complex the primary keys get for the lower levels, as the keys from the previous levels all 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 may be set to transient if desired).

When there is a name conflict, or a conflict with a reserved word, Autonomous REST Connector will post-pend a "_" to the name, or a "_<number>". This may make mapping difficult, as it may be hard to tell which is which.