How to extract key value from a .json array?

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}, ‘’) This uses the dot notation ‘’ 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.


ifelse(isNull({jarry}),'no value found',

Kind regards,