Calculation field parsing JSON

I am trying to create a calculation field in dataset that parses JSON that contains “::”.
However when I try to use in visualization, the data is getting a syntax error. The JSON data is correct and the calculation works when I try to parse other elements but when I try to select the element that contains the “::” it stops working… I did try escaping the “::” by doing “::” but no luck… Any help much appreciated!
Here is the calcuration that is giving a syntax error : parseJson({JSON}, “$[0].fieldData.Map::State”)

Can you show me an example of your JSON with :: ?

And is that :: necessary? You could replace all instances of :: as a string before with this replace({JSON}, “::”, “:”).

So it might look like this eventually:

parseJson(replace({JSON}, “::”, “:”), “$[0].fieldData.Map.State”)

Let me know if that works

Hi, @yuichim. Did @Max’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!


Hi Max. Thanks for reply. The “::” was created by an application that created JSON but does not need to have the “::” for the report. As you mentioned, the replace function did work. For the final result, I ended up creating a script in that application to export a much cleaner JSON file. I truly appreciate for your help!

1 Like