Hi all,
I have a field in QS called priceitems of string datatype that can also take NULL’s. There are records that priceitems have values like [{“id”:27,“amount”:6.50,“explainer”:null,“quantity”:1.00000000,“quantityunit”:0,“total”:6.50,“billable”:false,“name”:“Catering”,“taxable”:false,“servicecost_servicecostid”:38038}]
which is a .json inside . I want to create a calculated field and extract the value of the “name” key. How can I do this?
I currently create a calculated field with syntax
isNull({priceitems}), null, // Checks if priceitems is null, returns null if true
parseJson({priceitems}, '[0].name') // Parses priceitems to extract the name key if priceitems is not null
)
However, although the calculated field is created, then the dataset fails to refresh. WHat do I do wrong?
type or paste code here
type or paste code here
hi Fotis_flex,
The syntax for parsing JSON in QuickSight calculated fields uses dot notation rather than square brackets. Try updating your calculated field to: isNull({priceitems}), null, parseJson({priceitems}, ‘0.name’) This uses the dot notation ‘0.name’ rather than ‘[0].name’ to extract the name property from the first object in the JSON array. Let me know if updating the syntax helps resolve the refresh issue!
1 Like
Hi @imaitri . No this still crashes loading the dataset. It does not work. I wonder if the reason is that the values of the field priceitems uses [ ] initially instead of { }. According to documentation the field should start with { } instead of [ ]. And I can’t find a way to extract the key values. Any other suggestions?
Hello @imaitri Do you perhaps have any more ideas regarding my above comment? Thx
@Fotis_flex ,
In the dataset preparation mode, the parse json does work as @imaitri explained by using the dot notation.
calculation
ifelse(isNull({jarry}),'no value found',
parseJson({jarry},"$.0.name"))
Kind regards,
Koushik
