Having trouble with replace function on a JSON extracted string

Question & Answer
,
1

Hello!

I’m attempting to create a calculated field in my dataset that uses the replace function to strip escape characters () from a string field. I’m currently receiving an error message when attempting to create this calculated field. It tells me:

function replace has incorrect argument type replace(<STRUCTURED>, String, String). Function syntax expects String, String, String

I’m not sure if it’s relevant but the field I am passing as the first parameter to replace is a string field that I used JSON_EXTRACT on to pull from my underlying SQL database into SPICE.

Any guidance would be appreciated as to whether I’m incorrectly using part of the QuickSight functionality here or if this is a problem I need to solve further upstream (either in my custom query or in the SQL database I import from).

Thank you!

2

Hi @condor19
looks like the first field you are using is not a string data type or is not recognized as.
Can you check the data type?
BR

3

Hi @ErikG, thanks for the reply!

When I select the field from the list in the “Create Calculated Field” screen, it shows as role: dimension, type: string.

Is there somewhere else I should be checking that data type?

4

Hi @condor19 - Welcome to AWS QuickSight community and thanks for posting the question. Is it possible to share some sample input data and expected output?

This will help community members to understand the issue and provide the right path for the solution.

Regards,
Sanjeeb

5

Hi @Sanjeeb2022, certainly! I’ll break this down into a few parts.

First, there’s the data as it’s stored in my source (a JSON column of a MySQL database). It looks something like this:

{"other": false, "trial": false, "more-info": false, "challenging": true, "custom-answer": "I would like to have an 8-9\" tree sapling. The 16\" is too large for the spot I want to plant it and does not fit in the trunk of my car."}

Next, I have a QuickSight Dataset that extracts this column (and others) using a custom SQL query that looks like this:

select other_fields..., JSON_EXTRACT(ir.rejection_context, '$.challenging') as 'Challenging', JSON_EXTRACT(ir.rejection_context, '$."custom-answer"') as 'Custom Feedback', JSON_EXTRACT(ir.rejection_context, '$.trial') as 'Trial', ...

The above query gives me a dataset field for Custom Feedback that contains the following:

"I would like to have an 8-9\" tree sapling. The 16\" is too large for the spot I want to plant it and does not fit in the trunk of my car."

I’d like to remove the escape characters after each of the heights (8-9 and 16) in this string but when I attempt to create a calculated field on the dataset using the following:

replace({Custom Feedback}, "\\", "")

then I receive the error detailed in my initial post.

Strangely, if I attempt to create a calculated field on an analysis instead of a dataset using the same function, it works just fine but this is not ideal as my team members want to be able to access this data without creating a calculated field on each analysis they build.

Thank you for your time and I look forward to your response!
Connor