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.