Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
cancel
Showing results for 
Search instead for 
Did you mean: 
rp2018
Creator
Creator

How to remove surrounding [] and double quotes when extracting JSON data?

We have a file which contains JSON data.  Using tFileInputJson component to extract data from it and some of the fields are surrounded by [ ] and double quotes.  What is the best method to remove surrounding characters?

 

 

0683p000009M8id.png

 

Output:

0683p000009M8tB.png

 

InputFile:

{
"Options": [
{
"Value": 111111,
"Color": "#0000ff",
"IsManaged": false,
"ExternalValue": "",
"ParentValues": [],
"MetadataId": null,
"HasChanged": null,
"Label": {
"LocalizedLabels": [
{
"Label": "United States",
"LanguageCode": 1033
}
]
},
"Description": {
"LocalizedLabels": [
{
"Label": "TestDescription1"
}
]
}
},
{
"Value": 222222,
"Color": "#0000ff",
"IsManaged": false,
"ExternalValue": "",
"ParentValues": [],
"MetadataId": null,
"HasChanged": null,
"Label": {
"LocalizedLabels": [
{
"Label": "Canada",
"LanguageCode": 1033
}
]
},
"Description": {
"LocalizedLabels": [
{
"Label": "TestDescription2"
}
]
}
}
]
}

 

 

Labels (3)
2 Replies
TRF
Champion II
Champion II

It should be possible (?) within the tFileInputJSON but at least you can change the value using a tMap with this regex (not verified) for the desired fields:
row1.Label.replaceAll("[\[\]\"", "")
Anonymous
Not applicable

I think this might be because you have a wildcard in your JSONPath for the arrays. If you change the * to a 0 in [] for your values, this should remove the brackets. Of course, it will only return the first instance of the array.