How do I get values from a JSON object for the defined path
|
|
This example demonstrates getting the required values from the JSON object by providing a JSON path.
In the solution shown below, the JSON object and the JSON Path are stored into the variables using the "store" command. A dataset is created using the "ddCreateDataset" command and the value is extracted for the defined path into it using "jsonExtractValuesByPaths" command. The value is fetched from the dataset using "ddGetCellValueByIndex" command.
Note: The notation to reuse the value of the variable in Worksoft SaaS is ${<Variable Name>}.
Solution:
| # | Command | Target | Value |
|---|---|---|---|
| Store the JSON object in a variable | |||
| 1 | store | {"zipcodeDetails":[{"zipcode":90001,"state": "California"},{"zipcode":10001,"state": "New York"}]} | JSON |
| Store the JSON Path in a variable | |||
| 2 | store |
$.zipcodeDetails[0].zipcode |
JSONPath |
| Declare the dataset name with a data definition specified in the value field containing the column name 'zipcode' where the values from the JSON object will be stored | |||
| 3 | ddCreateDataset |
JSONValuesDataset |
JSONValuesDataDefinition(W) |
| Extract the values from the specified JSON path into the dataset | |||
| 4 | jsonExtractValuesByPaths |
${JSON}||${JSONPath} |
JSONValuesDataset |
| Fetch the value that is stored in the first row and first column of the dataset | |||
| 5 | ddGetCellValueByIndex |
JSONValuesDataset(0,0) |
zipcode_1 |
Tips, Tricks, Gotchas & Best Practices:
- You can also call JSON and JSON Path directly in the target field separated by || symbol instead of storing them into a variable.
- If you have multiple JSON paths, you can either provide all the paths in a comma-separated fashion and use the command "jsonExtractValuesByPath" at once and get the values in multiple columns within a single row or you can use the command 'n' number of times for 'n' number of JSON paths. The latter approach will create a row in every occurrence of the command and the values for every path will be extracted in a new row.
- While declaring the Dataset it is necessary to validate that the Data Definition specified in the value field has a required number of attributes as this would decide the number of columns in the Dataset. If the number of values returned for the given JSON path are more than the columns in the Dataset, the command fails with the error "The Number of columns returned are more than the number of attributes in the Data Definition".
- For the command "ddCreateDataset", the status of the Data Definition can be optionally passed within braces along with the name of the Data Definition in the value field. Allowed values for status are 'W'(WIP-Work In Progress) and 'A'(ACTIVE). If not passed, the status of the Data Definition is assumed to be 'W'.
- As a best practice, it is always advisable to define the variable names without any spaces and if you want to differentiate the words, use underscores.
|
|
|