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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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.