Amazon QuickSight – parseJson
The parseJson function in Amazon QuickSight parses a JSON string from a field and returns the value at the specified path.
Syntax
#Start# parseJson(fieldName, path) #End#
This function takes two arguments:
- fieldName (required): the name of the field containing the JSON string to parse.
- path (required): the path to the value to extract from the parsed JSON object.
Suppose you have a dataset containing a JSON string in one of the fields. You want to extract a specific value from the JSON string and use it in your analysis.
Assume the following dataset:
| Item | Data |
| Apple | {“price”: 0.99, “quantity”: 10} |
| Banana | {“price”: 1.25, “quantity”: 5} |
| Orange | {“price”: 0.89, “quantity”: 7} |
| Pear | {“price”: 1.50, “quantity”: 12} |
To extract the price from the JSON string in the Data field, you can use the parseJson function:
- Select the dataset that you want to create a calculated field for.
- Click on the “Add field” button.
- Enter the following expression:
Example
#Start# parseJson("Data", "$.price") #End#
This expression parses the JSON string in the Data field and extracts the value at the “price” path. The resulting calculated field will contain the prices extracted from the JSON string.
Click “Create field” to create the calculated field.
After creating this calculated field, you can use the extracted prices for visualizations and analysis. In this example, the calculated field would have the following values:
| Item | Data | Price |
| Apple | {“price”: 0.99, “quantity”: 10} | 0.99 |
| Banana | {“price”: 1.25, “quantity”: 5} | 1.25 |
| Orange | {“price”: 0.89, “quantity”: 7} | 0.89 |
| Pear | {“price”: 1.50, “quantity”: 12} | 1.50 |
Note that the “Price” column is a calculated field, and the values are not stored in the original dataset. Also, note that the path to the value in the JSON string is specified using the dot notation.