A JSON map is a collection of key-value pairs that contain a set of unique keys. Typically, the keys are used for reference and, therefore, act as identifiers with a real world relationship, such as ID numbers, dates, or times. The driver will attempt to detect maps by recognizing patterns and formats in the keys when sampling an endpoint. For the driver to automatically recognize an object as a map, the object must have the following characteristics:
  • The keys must me be one of the following types:
    • Numeric values
    • GUIDs
    • Dates formatted as YYYY-MM-DD
    • Times using the ISO format. The map may contain values with and without timezones in the same map.
    • Timestamps using the ISO format. The map may contain values with and without timezones in the same map.
  • Every key in the object must be of the same type.

For example, the following JSON document contains a map that uses dates as keys:

{
 "1979-10-31":{"attendance":"12080","opponent":"Wildcats","result":"loss"},
 "1979-12-06":{"attendance":"34000","opponent":"Mustangs","result":"loss"},
 "1979-11-06":{"attendance":"8500","opponent":"Jets","result":"loss"},
}

The driver normalizes the key-value pairs in the JSON map to a child table. The fields in the map value are mapped into relational columns. For example, the attendance and opponent fields, are mapped to relational columns of the same name. The primary key is determined by the key portion of the key-value pair and maps to the KEY column by default.

When mapping the schema, the driver normalizes the key-value pairs in the JSON map to a child table. The fields in the map value are mapped into relational columns. For example, the attendance and opponent fields, are mapped to relational columns with corresponding names. Similarly, the key portion of the key-value pair is mapped to the KEY column by default. The primary key is determined by the type of the key column. For key values that are numeric values or GUIDS, the KEY column is used as the primary key. For key values that are date, time, or timestamp values, the driver generates primary keys in the ROWID column to avoid the possibility of non-unique primary keys (See “Determining the primary key” for more information).

The name of the parent table is derived from the endpoint from which the data was sampled. The parent table for our example is named SEASON_RESULTS and takes the following form:

Table 1. SEASON_RESULTS
ROWID (PK) KEY ATTENDANCE OPPONENT RESULT
unique_id 1979-10-31 12080 Wildcats loss
unique_id 1979-12-06 34000 Mustangs loss
unique_id 1979-11-06 8500 Jets loss