Exposing Data in JSON Format

JSON mapping support in data services is available as another result type in the output mappings section of the data services query. That is, you can specify JSON as the output type for the query and then provide the JSON template that should be used for service responses. 

Basic Mapping

The following is a sample JSON mapping of the data services results. Here, we can see the JSON template, which is the format that is expected to be returned from our query. 

{
    "customers": {
        "customer": [
            {
                "phone": "$phone",
                "city": "$city",
                "contact": {
                    "customer-name": "$customerName",
                    "contact-last-name": "$contactLastName",
                    "contact-first-name": "$contactFirstName"
                }
            }
        ]
    }
}

As shown in the sample given above, the column name values that are expected in the query result should be referred to by the column name with the "$" prefix. E.g. "$customerName".

Also, the structure of the JSON template should follow some guidelines in order to be compatible with the result. These guidelines are:

• The top most item should be a JSON object. It cannot be a JSON array.
• For handling multiple records from the result set, the immediate child of the top most object can be a JSON array, and the array should contain only a single object.
• If only a single result is returned, the immediate child of the top most object can be a single JSON object.
• After the immediate child of the top most object, there cannot be other JSON arrays in the mapping.

Extended Properties for Mapping Fields

In a basic JSON output mapping, we specify the field values that we expect in the query result. You can give additional properties to this field mapping, such as data type of the field, the possible content filtering user roles etc. These extended properties for the fields are given in parentheses, with a list of string tokens providing the additional properties, separated by a semi colon (";").

Data Types

The example given below shows how the 'data type' property is added to the 'age' field using parentheses "()".

{ "age" : $age(type:integer) }

• The extended property 'type' is given along with the value 'integer'. 
• The property and the value are separated by a colon. 
• Note that the possible values for data type are "integer", "long", "double" and "boolean".

Content Filtering (Required Roles)

The example given below shows how the 'data type' extended property as well as the 'required roles' extended property is added to the 'age' field using parentheses "()".

{ "salary" : $salary(type:double;requiredRoles:hr,admin) }

• The 'requiredRoles' property is added along with the values 'hr' and 'admin'. 
• Note that the two values for the 'requiredRoles' property are separated by a comma. 
• Also, the two extended properties are separated by a semicolon. 

Nested Queries

Nested queries can be mentioned in the JSON mapping by giving the query details as a JSON object attribute. That is, the name of the target query to be called and the property value (the fields in the result mapped with the target query parameters) are included in the JSON mapping as the object attribute name. Example:

{
    "Offices": {
        "Office": [
            {
                "officeCode": "$officeCode(type:integer)",
                "city": "$city",
                "country": "$country",
                "phone": "$phone",
                "@employeesInOfficeSQL": "$officeCode->officeCode"
            }
        ]
    }
}

In the nested query mapping,

• The target query name is mentioned by prefixing the query name with "@". Note "@employeesInOfficeSQL" in the example given above.
• The parameter mapping is added to the query by giving the following values: The field name in the result prefixed by "$", and the name of the target query parameter.
• These two values in the parameter mapping are separated by "->". See "$officeCode->officeCode" in the example given above.
• Note that the target query name and the parameter mapping are separated by a colon as follows: "@employeesInOfficeSQL": "$officeCode->officeCode"

A JSON Mapping Sample can be found here: JSON Mapping Sample.