Using JSON with Data Services

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.

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.
9. Save the query.

GET data in JSON

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

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:

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

Example:

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.

{"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 Path	Employee
  Resource Method	POST
  Query ID	AddEmployeeDetails

• Resource for updating employee information:

  Resource Path	Employee
  Resource Method	PUT
  Query ID	UpdateEmployeeDetails

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.

   {
     "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:

   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.

1. First, create a file named employee-batch-payload.json, and define the JSON payload for posting multiple employee records (batch) as shown below.

   {
       "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:

   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.

   {
     "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:

   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.

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.

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

   {
    "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:

   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