parseJson returns the data with double quote

Hi there,

I am trying to get the value from the jsonString in the dataset.
When I use parseJson({jsonString}, “$.status”); it returns the value with a double quote.

in this case
jsonString = {"time":1679986416,"status":"closed"}
I get a "closed" result instead of closed

I found an old issue where it was closed because of no activity.

replace(parseJson({jsonString},"$.status"),'"','') did the job to get the actual value, but is that expected behavior?

Hi @neelay - Can you try the recommendation provided by old blog, essentially it is replacing double quote by null so should be fine. Please test it at your end and confirm.

Regards - Sanjeeb

1 Like

@Sanjeeb2022 I have a workaround working, I think I have mentioned that in the ticket.

But thanks anyway…

1 Like

Hi @neelay - Thanks, can you please share the solution to the community. I will learn from you.

Regards - Sanjeeb

1 Like

Hi @neelay: I’m glad the workaround you found in the post you mentioned got you the result that you were looking for. I believe that your question about if this is an expected behavior or not will be determined by the input field’s data type. The parseJSON functions documentation mention the function expects different data types, based on the “dialect”. It could be that you are seeing some implicit conversion of your JSON to a string. Could you please confirm the source dialect and data type?

Thanks.
Chris

1 Like

@cmruiter
Thanks for the response.
I am using a custom query to rds (MySQL) to fetch the data, and the column data type is TEXT. And MySql returns that {"time":1679986416,"status":"closed"} as a String as well.

The query looks like this
Select Field1, Field2, jsonField, ... From Table1 join Table2 ... Where Table1.Field2 = "xyz"

Thanks
Neelay

1 Like

Hi @neelay ,

I threw together a quick example using both text and json data types in a aurora mysql instance and tried doing a calculated field in both the analysis and the data set and I am seeing the same results that you are. (String value being returned with double quotes, in the analysis.)

Thank you for letting us know about this issue. I recommend opening a support case, so the QuickSight product team can take a deeper dive into this issue.