This documentation is work in progress and will be released with the next WSO2 EI version.
Page Comparison - Using JSON with Data Services (v.71 vs v.72) - WSO2 Enterprise Integrator 6.x.x - WSO2 Documentation

All docs This doc

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

You can send and receive JSON messages by default via WSO2 Enterprise Integrator's (WSO2 EI) ESB profile. See the topics given below to understand how data can be exposed in the JSON format, and how data can be changed by sending JSON payloads. In this tutorial, you will use a data service that exposes RDBMS data.

Tip
titleBefore you begin!

If you have not tried the Exposing a Datasource as a Data Service tutorial previously follow the steps given below:

  1. Download the RDBMSDataService from here.
  2. Download the product installer from here, and run the installer.
    Excerpt Include
    Installing the Product
    Installing the Product
    nopaneltrue
  3. Download the JDBC driver for MySQL from here. Unzip it, get the <MySQL_HOME>/mysql-connector-java-8.0.16.jar JAR, and place it in the <EI_HOME>/lib directory.

    Note

    If the driver class does not exist in the relevant folders when you create the datasource, you will get an exception, such as 'Cannot load JDBC driver class com.mysql.jdbc.Driver'.

  4. Start the WSO2 ESB profile.

    Panel
    borderColor#542989
    bgColor#ffffff
    borderWidth1
    Localtab Group
    Localtab
    activetrue
    titleOn MacOS/Linux/CentOS

    Open a terminal and execute the following command:

    Code Block
    wso2ei-6.4.0-integrator
    Localtab
    titleOn Windows
    Go to Start Menu -> Programs -> WSO2 -> Enterprise Integrator 6.4.0 Integrator. This will open a terminal and start the ESB profile.
  5. Go to product's management console: https://localhost:9443/carbon
  6. Enter admin as the username and password.
  7. Click Add > Data Service > Upload.
  8. Browse and add the RDBMSDataService.dbs file you downloaded.

Table of Contents
maxLevel4
minLevel3

A data service can expose data in one of the following formats: XML, RDF, or JSON. You can select the required format by specifying the output type for the data service query. To expose data in JSON, you need to select JSON as the output type, and map the output to a JSON template.  

Map the output type to JSON

The data service we uploaded or created previously, maps the output type to XML. Follow the steps given below to change it to JSON.

  1. Open the ESB profile's Management Console using https://localhost:9443/carbon, and log in using admin as the username and the password.

  2. Click List under Main > Services. The RDBMS data service should be listed.
  3. Click the data service to open the Service Dashboard.
  4. Click Edit Data Service (Wizard) to open the data service using the Create Data Service wizard.
  5. Click Next until you get to the Queries screen.
  6. Edit the GetEmployeeDetails query.
  7. Change the Output Type to JSON
  8. You can now start defining the JSON template for the output. Listed below are a few sample templates that you can use for this query.

    Localtab Group
    Localtab
    activetrue
    titleSimple JSON template

    Shown below is a basic mapping.

    Code Block
    languagejavascript
    titleSample JSON Mapping
    { "Employees":
          {"Employee":[
            {"EmployeeNumber":"$EmployeeNumber",                        
             "Details": {
              "FirstName":"$FirstName",
              "LastName":"$LastName",
              "Email":"$Email",
              "Salary":"$Salary"
             }
            }                  
          ]
        }            
    } 
    Tip

    Note the following:

    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. "$EmployeeNumber".

    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.

    All JSON responses are returned as an array.

    Localtab
    titleDefine data types

    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 semicolon (";"). See the sample below.

    Code Block
    languagejavascript
    titleSample JSON Mapping
    { "Employees":
          {"Employee":[
            {"EmployeeNumber":"$EmployeeNumber(type:integer)",                        
             "Details": {
              "FirstName":"$FirstName",
              "LastName":"$LastName",
              "Email":"$Email",
              "Salary":"$Salary(requiredRoles:hr,admin)"
             }
            }                  
          ]
        }            
    }
    Tip
    Setting data types
    • The 'data type' property is added to the EmployeeNumber field using parentheses ().
    • 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 'data type' extended property, as well as the 'required roles' extended property, is added to the 'Salary' field using parentheses "()".
    • 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. 
    Localtab
    titleNested queries

    If you want to write a nested query using JSON, see the tutorial on working with nested queries.

  9. Save the query.

GET data in JSON

The RDBMSDataService that you are using already contains the following resource:

Resource PathEmployee/{EmployeeNumber}
Resource MethodGET
Query IDGetEmployeeDetails

You can now RESTfully invoke the above resource. To send a JSON message to a RESTful resource, you can simply add the “Accept:Application/json” to the request header when you send the request. The service can be invoked in REST-style via curl
Shown below is the curl command to invoke the GET resource:

Code Block
curl -X GET -H "Accept: application/json" http://localhost:8280/services/RDBMSDataService/Employee/{EmployeeNumber}

Example:

Code Block
curl -X GET -H "Accept: application/json" http://localhost:8280/services/RDBMSDataService/Employee/1

As a result, you receive the response in JSON format as shown below.

Code Block
languagejs
{"Employees":{"Employee":[{"EmployeeNumber":"1","FirstName":"John","LastName":"Doe","Email":"JohnDoe@gmail.com","Salary":"10000"},{"EmployeeNumber":"1","FirstName":"John","LastName":"Doe","Email":"JohnDoe@gmail.com","Salary":"20000"}]}

POST/UPDATE data using JSON

When a client sends a request to change data (POST/PUT/DELETE) in the datasource, the HTTP header Accept should be set to application/json.  Also, if the data is sent as a JSON payload, the HTTP header Content-Type should be set to application/json.

The RDBMSDataService that you are using already contains the following resources for adding and updating data.

  • Resource for adding employee information:

    Resource PathEmployee
    Resource MethodPOST
    Query IDAddEmployeeDetails
  • Resource for updating employee information:

    Resource PathEmployee
    Resource MethodPUT
    Query IDUpdateEmployeeDetails

You can RESTfully invoke the above resource by sending HTTP requests as explained below.

Post data

To post new employee information, you need to invoke the resource with the POST method.

  1. First, create a file named employee-payload.json, and define the JSON payload for posting new data as shown below.

    Code Block
    {
      "user_defined_value": {
        "EmployeeNumber" : "14001",
        "LastName": "Smith",
        "FirstName": "Will",
        "Email": "will@google.com",
        "Salary": "15500.0"
      }
    }
  2. On the terminal, navigate to the location where the employee-payload.json file is stored, and execute the following HTTP request:

    Code Block
    curl -X POST -H 'Accept: application/json'  -H 'Content-Type: application/json' --data "@employee-payload.json" -k -v http://localhost:8280/services/RDBMSDataService/Employee

Post data in batches

You are able to post JSON data in batches using the RDBMSDataService that you created or uploaded.

Info

To verify that batch requesting is enabled:

  1. Log in to the EI Management Console.
  2. Click List under Main > Services and select the RDBMSDataService.
  3. Click the data service to open the Service Dashboard.
  4. Click Edit Data Service (Wizard) to open the data service using the Create Data Service wizard.
  5. See that the Batch Requesting check box is selected.
  1. First, create a file named employee-batch-payload.json, and define the JSON payload for posting multiple employee records (batch) as shown below.

    Code Block
    {
        "user_defined_value": {
            "user_defined_value": [
                {
                    "EmployeeNumber": "5012",
                    "FirstName": "Will",
    				"LastName": "Smith",
                    "Email": "will@smith.com",
                    "Salary": "13500.0"
                },
                {
                    "EmployeeNumber": "5013",
    				"FirstName": "Parker",
                    "LastName": "Peter",
                    "Email": "peter@parker.com",
                    "Salary": "15500.0"
                }
            ]
        }
    }
  2. On the terminal, navigate to the location where the employee-batch-payload.json file is stored, and execute the following HTTP request:

    Code Block
    curl -X POST -H 'Accept: application/json'  -H 'Content-Type: application/json' --data "@employee-batch-payload.json" -k -v http://localhost:8280/services/RDBMSDataService/Employee_batch_req

Update data

To update the existing employee records, you need to invoke the resource with the PUT method.

  1. First, create a file named employee-upload-update.json, and define the JSON payload for updating an existing employee record as shown below. 
    For example, change the salary amount. Make sure that the employee number already exists in the database.

    Code Block
    {
      "user_defined_value": {
        "EmployeeNumber" : "1",
    	"FirstName": "Will",
        "LastName": "Smith",
        "Email": "will@smith.com",
        "Salary": "78500.0"
      }
    }
  2. On the terminal, navigate to the location where the employee-upload-update.json file is stored, and execute the following HTTP request:

    Code Block
    curl -X PUT -H 'Accept: application/json'  -H 'Content-Type: application/json' --data "@employee-upload-update.json" -k -v http://localhost:8280/services/RDBMSDataService/Employee

Post data using Request Box

When the Request Box feature is enabled, you can invoke multiple operations (consecutively) using one single operation. The process of posting a JSON payload through a request box transaction is explained below.

Info

To verify that batch requesting is enabled:

  1. Log in to the EI Management Console.
  2. Click List under Main > Services and select the RDBMSDataService.
  3. Click the data service to open the Service Dashboard.
  4. Click Edit Data Service (Wizard) to open the data service using the Create Data Service wizard.
  5. See that the Enable Boxcarring check box is selected.
  1. First, create a file named employee_request_box_payload.json, and define the JSON payload for posting multiple employee records (batch) as shown below.

    Tip

    The following payload works for this use case. When you create payloads for different use cases, be mindful of the tips given here.

    Code Block
    {
     "request_box"  : { 
          "_postemployee" : {
    	  			"EmployeeNumber"  : "14005", 
    				"LastName" :  "Smith" ,
    				"FirstName" :  "Will" , 
    				"Email" :  "will@google.com" ,
    				"Salary" : "15500.0"
                			},
          "_getemployee_employeenumber":{
        			"EmployeeNumber"  : "14005"
               }
        }
    }
  2. On the terminal, navigate to the location where the employee_request_box_payload.json file is stored, and execute the following HTTP request:

    Code Block
    curl -X POST -H 'Accept: application/json'  -H 'Content-Type: application/json' --data "@employee_request_box_payload.json" http://localhost:8280/services/RDBMSDataService/request_box

Anchor
JSON_payloads
JSON_payloads

Tip
titleCreating JSON payloads for Request Box transactions

Note the following when you define a JSON payload for a request box transaction: The object name specified in the payload must be in the following format: "_<HTTP_METHOD><RESOURCE_PATH>" where RESOURCE_PATH represents the path value specified in the data service resource. For example, if the RESOURCE_PATH is "employee", the payload object name should be as follows:

  • For HTTP POST requests: _postemployee
  • For HTTP PUT requests: _putemployee

The child name/values of the child fields in the payload should be the names and values of the input parameters in the target query.

Handling a resource path with the "/" symbol

If the RESOURCE_PATH specified in the data service contains the "/" symbol, be sure to replace the "/" symbol with the underscore symbol ("_") in the payload object name. 

Note

Important! In this scenario, the RESOURCE_PATH value should only contain simple letters. For example, the value can be "/employee/add" but not "/Employee/Add".

For example, if the  RESOURCE_PATH  is /employee/add, the payload object name should be as follows:

  • For HTTP POST requests: _post_employee_add
  • For HTTP PUT requests: _put_employee_add