parseJson in Spice Dataset Calculated Field causes Error in Analysis

[ColoradoKiwi]

Hi,
We are having a similar issue to that described in a post from Maheshj here.

We have a SPICE dataset that is backed by an Athena view of an AWS Glue table. One of the fields injested by the DataSet is a string field containing JSON. Within the DataSet, we have a Calculated Field defined (via the UI, not in the custom SQL) that uses the parseJson() function to extract a specific value from the JSON string into a new DataSet column.

After saving the Calculated Field, and Applying, and Saving/Publishing the SPICE Dataset, we see the new column in the Preview pane of the DataSet, and it is correctly displaying the value expected that was parsed out of the JSON. All good.

However, when we try to use that Calculated Field from the DataSet in an Analysis, the analysis error’s with “Your calculated field expression contains invalid syntax”.

If we recreate the same DataSet as a Direct Query dataset rather than SPICE, add the same Calculated Field that uses parseJson(), again we get the right results in the DataSet preview, and resulting Calculated Field can be used fine in an Analysis (that uses the Direct Query dataset instead of the SPICE one).

We noted the comment in the QS docs for parseJson() that says “If your dataset is stored in QuickSight SPICE, you can use parseJson when you are preparing a data set, but not in calculated fields during analysis.”, and we thought that is what we were doing (?).

We didn’t see a “Resolution” posted on the other Community question referred to above (but its closed/locked), so were wondering if anyone else has had this issue, and what the resolution might be.
Thanks.

[andres007]

Hello @ColoradoKiwi,

Welcome to the QuickSight Community! It is great having you here!

Is it possible to share the JSON with some mock up data and the calculation you are performing so I can test in my side?

Also, have you tried QuickSight Arena? Here, you can build a dashboard with sample data and your calculations and then the community can help you a lot easier looking at the same issue.

NEW -- QuickSight Arena -- An embedded instance of QuickSight within the QuickSight Community

[FeatureTile 11(2)] QuickSight Arena provides you with an embedded instance of QuickSight right within the QuickSight Community. Using Arena, you can: Publish dashboards to the Community Gallery to showcase your skills Include dashboards depicting your use case while posting questions in the Community Q&A Clone underlying analysis views of published dashboards in order to learn / try answering questions Include dashboards, with working solutions, while responding to questions In this artic…

[EnriqueS]

"Thank you for letting us know. I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case.

If your company has someone who manages your AWS account, you might not have direct access to AWS support and Will need to raise an internal ticket to your IT team or whomever manages your AWS account. They Should be able to open an AWS Support case on your behalf.

Hope this helps!