Fetching Specific Fields From Database Table
In this blog, let us discuss how to capture and fetch those fields that are coming in the request profile from a Database table.
- We would be exposing an API that would be getting a request that contains some fields.
- Here, to capture the fields from the request we would be using scripting.
- For fetching the records from the database, we have used a connector Database V2 which allows a query to be passed dynamically.
First, let us see the payload that would be the request:
{
“requestCount”: 2,
“requests”:[
{
“studentId”: “abc”,
“fields”: [“emailId”,”percentage”,”grade”,”firstName”,”lastName”,”studentId”,”branch”,”YearOfJoining”]
},
{
“studentId”: “xyz”,
“fields”: [“emailId”,”percentage”,”firstName”]
}
]
}
From this payload, we need to fetch the records according to the Student ID from the database and only those fields should be coming in the output which is present in the fields.
Sample Response:
{
“Result”: [
{
“emailId”: “ad.2@gmail.com”,
“percentage”: “90”,
“grade”: “A”,
“firstName”: “Adam”,
“lastName”: “Levine”,
“studentId”: “abc”,
“branch”: “Science”,
“YearOfJoining”: “2019”
},
{
“emailId”: “rick.77@gmail.com”,
“percentage”: “71”,
“firstName”: “Rick”
}
]
}
Now, let us see the steps to achieve this.
Step 1: First, create a process with a start shape with the Web Services Server connector.

Step 2: Configure the Operation related to the Web Service Server Connector.
Note: Here the request profile and Response Profile are optional.

Step 3: Now, we need to drag and drop a Data Process shape for splitting the incoming request.

Step 4: We need to configure the Data Process for Splitting, first we have to select the profile type as JSON.

Step 5: We need to select the JSON Profile that needs to be split.

Step 6: Now, we need to select the split element with which you need to split and that element should be repeating.

Step 7: Now, we need to drag and drop the Data Process Shape for scripting.

Step 8: In Data Process, you need to choose custom scripting with inline script or process script component if you need it to be reused.

Step 9: The script that should be written is:

- Here, in the script we are using a JSON Slurper for parsing the JSON and we will take the JSON Object, and using this JSON Object we would be capturing the Fields that are coming in the request.
- Once it is captured, we would separate each field with a comma and it is stored in dynamic document property named fields.
Step 10: Drag and drop a Set Properties for capturing the ID.

- You would be able to capture the ID with a dynamic document property from the output coming from the data process.
Step 11: Drag and drop a message shape for capturing the Dynamic Query which needs to be passed to the Database Connector.

Step 12: You need to use placeholders for passing the ID and SQL Query to the Database connector in a JSON Format.

Step 13: We need to drag and drop a Database V2 connector for fetching the records from the Database.

Step 14: Now, we need to configure the connection related to Database V2 and the action as get.

Step 15: Now, we need to configure the operation related to Database V2.
- First, we need to perform import, and while importing
- The get operation type should be Standard Get.
- Enable SQL Query should be checked.


Step 16: Now, we need to Add 2 parameters in the Database V2 Connector.

- The first parameter would be the SQL Query and the Second parameter would be the Student ID based on which we need to fetch the records.
Step 17: We need to drag and drop a Message shape for combining the JSON Records that are coming as the output of the Database V2 connector.

Step 18: Inside the message shape we need to add a placeholder and a comma, combine documents into a single message checkbox should be ticked.

Step 19: Drag and drop another message shape to format it into a JSON.

Step 20: Drag and drop another message shape to format it into a JSON.

Step 21: Drag and drop a Data Process shape to make the payload a well-formed JSON.

Step 22: In the Data Process, you need to choose the search and replace option where you can specify,(?=[^{}]*\]) this regex in the text to find section and Null in the replace with section.

Step 23: Finally, you need to add a Return document shape for sending the response back.

Step 24: Now, the process is done and you need to create a package component and deploy the process to an environment. Once deployed, you can test it using any testing tool like Postman by using a POST request with the input payload in the body.
Request from Postman

Response
