Step-By-Step Guide to Use JSON to Retrieve a Key-Value Pair

Example 1: To retrieve the data from the JSON input:

 

Let's consider an example of passing the JSON input manually and retrieving key-value pair from a specific path.

We are passing a JSON input that contains a store having details of books and bicycles.  We would retrieve details of the book available at the third book array of the JSON and its author name.

1. Create a new process.
2. From the Canvas Tools panel, add the JSON Activity to the Flowchart designer on the Canvas.
3. In the Parameter bar, create In arguments, outputstring and authorname of type String. These arguments are used to store the book details and name of the author respectively.

 

 

4. In the JSON Action list, select Getter to perform the action of retrieving data.
4. Select the Manual Input Entry check box to provide the input manually.
4. Click the (Settings) icon, and then click Input JSON Editor to enter the JSON data manually. The JSONInputEditor dialog box appears.

 

 

7. Enter the details of the books and the bicycles in JSON format and then click Confirm. You are directed back to the JSON activity in the Canvas.
7. Click the (Settings) icon, and then click Output Mapping to align the JSON path and the data fetched with an argument. The Output Mapping dialog box opens.
7. Click Add to provide details related to the mapping of the output parameters.
7. In the JSONPath field, enter the JSON path expression, $..book[2] to retrieve the details of the third book available in the JSON array.
7. In the Mapping Variable list, select outputstring defined in the Parameter bar. The mapped argument stores the data retrieved from the stated JSON path.
7. Click Add.
7. In the JSONPath field, enter the JSON path expression, $..book[2].author to retrieve the author name of the retrieved book.
7. In the Mapping Variable list, select authorname defined in the Parameter bar. The mapped argument stores the data retrieved from the stated JSON path.
   
   

 

15. Click CONFIRM to save the output mapping configuration.
    To view the output in Automation Studio, let's add WriteLine activity for both the retrieved set of data. You can assign this process to a robot, if you want to execute this process outside Automation Studio.
15. Add a WriteLine activity below the JSON Activity and in the Text field, enter outputstring to print the book details retrieved.
15. Add another WriteLine activity below the first WriteLine activity and in the Text field, enter authorname to print the name of the author of the retrieved book.
15. Save the process.
15. Setup the environment and then perform test run.

 

 

The console displays the book details and the name of the author.

 

Example 2: To retrieve the data from the JSON input in a tabular format:

Let's consider an example of passing the JSON input manually and retrieving a list of objects from a specific path. 

We are passing a JSON as an input that contains details of regions, in form of object array. We would retrieve details of the bounding box and text in a tabular format.

1. Create a new process.
2. From the Canvas Tools panel, add the Create DataTable activity to the Flowchart designer on the Canvas.
3. Create a data table named ResultTable. An Out argument of type DataTable gets automatically created for ResultTable.
4. Click the  (Settings) Icon and add two columns BoundingBox and Text. 
   
   
5. From the Canvas Tools panel, add the JSON Activity to the Flowchart designer on the Canvas and connect it to the Create DataTable activity.
   
   
6. From the JSON Action list, select Getter.
7. Select the JSON Input checkbox to get the Input JSON Editor option in settings.
8. Click the  (Settings) Icon and select Input JSON Editor.
9. Provide the JSON input in the editor.
   
   
10. Click the  (Settings) icon, and then click Output Mapping to align the JSON path and the data fetched with an argument. The Output Mapping dialog box appears.
11. Click Add to provide details related to the mapping of the output parameters. 
    
    
    
12. In the JSONPath field, enter the JSON path expression, $.regions[0].lines[*].words[*].boundingBox to retrieve the details for BoundingBox.
     

    NOTE:

    Use wild card (*) to retrieve multiple objects from the JSON.

13. In the Mapping Variable list, select ResultTable defined in the Parameter bar. 
14. In the DataTableColumn list, select BoundingBox column. The mapped column stores the data retrieved from stated JSON path.
     

    NOTE:

    The DataTableColumn list is populated, only if, the value selected under Mapping Variable is an argument of type Datatable.

15. Click Add.
16. In the JSONPath field, enter the JSON path expression, $.regions[0].lines[*].words[*].text to retrieve the details for Text.
17. In the Mapping Variable list, select ResultTable defined in the Parameter bar.
18. In the DataTableColumn list, select Text column.
19. Click CONFIRM to save the output mapping configuration. 
20. To verify the datatable is successfully populated, create an Export DataTable activity in and enter the required fields to export data table in an Excel  You can assign this process to a robot, if you want to execute this process outside Automation Studio.
    
    
21. Save the process.
22. Setup the environment and then perform test run.
    
    
    
    The exported excel displays the retrieved data.