I have a column in my dataset that contains JSON data. I’m trying to parse this column in Amazon Quick Sight to extract the convertedValue for a specific toCurrency passed as a parameter.
Here’s an example of the JSON structure in the column:
[
{“toCurrency”: “USD”, “exchangeRate”: 1.1653, “convertedValue”: 0.0},
{“toCurrency”: “GBP”, “exchangeRate”: 0.8686999997, “convertedValue”: 0.0},
{“toCurrency”: “CAD”, “exchangeRate”: 1.6079000006, “convertedValue”: 0.0}
]
I would like to extract the convertedValue for a given currency (e.g., “USD”), based on a parameter. I’m not using SPICE capacity, but even so, I’m unable to parse or extract the values correctly.
Can someone guide me on how to achieve this within Quick Sight?
parseJson function can extract values from a JSON object or from a JSON array using path expressions. However, when working with JSON arrays, parseJson allows extraction only by a static index (e.g., the first array element using “$.convertedValue”). It currently does not support dynamic filtering by an inner key (such as extracting the element where toCurrency equals a parameter value) within a single calculated field.
In SPICE datasets, you may use parseJson only during data preparation, not in analysis-level calculated fields. For Direct Query datasets, you can use parseJson in both data preparation and in analysis calculated fields.
Please refer to the below community posts this might be helpful for you.
Following up here as it’s been awhile since last communication took place on this thread; did you have any additional questions regarding your original post?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.