topic Re: How to remove surrounding [] and double quotes when extracting JSON data? in Talend Studio

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

rp2018 — Sat, 16 Nov 2024 03:26:42 GMT

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?

Output:

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"
}
]
}
}
]
}

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

TRF — Wed, 29 Jan 2020 10:54:57 GMT

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("[\[\]\"", "")

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

Anonymous — Wed, 29 Jan 2020 12:05:21 GMT

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.