parseJson field syntax error for Aurora Postgres JSON field on valid Json

Question & Answer
, , , ,
1

Attempting to create a custom field in either analysis or data view on an Aurora PostgreSQL JSON field as a direct query. It has been ingested a string type and is valid JSON, as shown below:

{"what": "worktop", "detail": {"one": "123", "two": "CA"}, "where": "kitchen"}

I am attempting to use parseJson({column_name}, "$.where") and am receiving a compilation error which has not got much information. When I look at the actual HTTP response for adding that calculated field I get the following:

{
    "errorCode": "SYNTAX_ERROR",
    "message": "ParseJson can be applied to a Json Field",
    "parameterName": null
}

I am going to dig into our database and try to find the actual query, in the meantime has anyone got any advice on what is causing this query to fail?

Thanks!

1 Like
2

Hello @Ben_Ashwell, welcome to the QuickSight community!

I just wanted to clarify, you are pulling in the field as JSON into QuickSight and then trying to parse it in a calculated field, am I correct?

If that is the case, that is technically possible in QuickSight, but I wouldn’t recommend it. Once it is ingested into a dataset, QuickSight does not contain a JSON field value type, so you are just retrieving a messy string value. I would recommend parsing the JSON in custom SQL on ingestion to retrieve the where column rather than attempting to manage it from a calculated field. I hope this helps!

3

Hi Dylan, thanks very much for your reply.

Yes, it’s being ingested from JSON in Postgres and is becoming a String in QS.

Your recommendation is to go back to the data source and instead of ingesting the column automatically do custom SQL to ingest it into multiple columns from that JSON field? Have you got any resources I could view on this?

Whilst I am happy to attempt your recommended approach, I am still a little confused why parseJson is not working, I believe I am using it as this link documents. Could you help me to understand why that is not working?

Thanks a lot!

4

Hello @Ben_Ashwell, rather than handling this in your database, I was thinking you could import the dataset into QuickSight with a custom SQL query to parse any required JSON.

Then you can build out all of the columns you need as you query the data. I do see that there is a parseJson calculated field in QuickSight, I have personally not used this aggregation before because I always make this updates in the database or upon ingestion through SQL.

I also see why this is not working for you. I found this from the documentation you linked:

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.

So, if you leave your dataset on Direct Query, you will need to do this in custom SQL. Otherwise, if you switch to SPICE, you should be able to add this calculated field on your dataset directly. I hope this helps!