topic Re: [resolved] Reading JSON with nested arrays in Talend Studio

[resolved] Reading JSON with nested arrays

Anonymous — Tue, 02 Dec 2014 04:32:17 GMT

Hi guys,
I'm trying to read in a file that has the following format:


},
{
  "productId": "9876",
  "name": "Test 2",
  "categories": 
}
]

I've used the tFileInputJSON to read in the data and productId and name fields are read perfectly. However, I can't seem to be able to read the categories array onto each record, or at the very least, a way to normalize it so that each record has one category? I originally tried selecting the "Get Nodes" checkbox, but it only ever outputs one value for the categories field and not all of them.
I've tried a number of other things but can't seem to get this to work.
Help please?
Thanks!
Chris

Re: [resolved] Reading JSON with nested arrays

Anonymous — Tue, 02 Dec 2014 05:13:50 GMT

Hi Chris,
JSON data given above is not getting parsed correctly ( http://json.parser.online.fr/)
... what is the significance of data inside last two square brackets ?
Vaibhav

Re: [resolved] Reading JSON with nested arrays

Anonymous — Tue, 02 Dec 2014 05:22:41 GMT

Hi,
Sorry, not sure how those two got in there as it's not supposed to be there. I've updated it now to remove them.
Thanks,
Chris

Re: [resolved] Reading JSON with nested arrays

Anonymous — Thu, 04 Dec 2014 15:53:01 GMT

Hi,
Can anyone help me with this?
There must be a way to parse out nested arrays from a JSON without having to read the file in more than once?
Thanks!

Re: [resolved] Reading JSON with nested arrays

Anonymous — Fri, 05 Dec 2014 01:37:23 GMT

Hi
Read categories node as a data and then you can use tExtractJsonField to extract data from the array.

Best regards
Shong

Re: [resolved] Reading JSON with nested arrays

Anonymous — Fri, 05 Dec 2014 13:15:52 GMT

Hi
Read categories node as a data and then you can use tExtractJsonField to extract data from the array.

Best regards
Shong

Hi Shong,
I can read the categories node in using a separate input component, but I'm trying to avoid having to load the same file twice as I have to iterate over a number of records for this.
I've been able to extract the node using the extractJSON component but I need to join it back to the main data too.
The tMap component won't allow me to join this back to the main data, as I'm guessing it will only take input from two separate streams of data? There has to be a better way to essentially flatten a JSON with a nested array?

Re: [resolved] Reading JSON with nested arrays

Anonymous — Thu, 11 Dec 2014 16:22:29 GMT

So what I've done now instead is to do
tFileInputJSON --> tExtractJSONFields
and set the Loop XPath query to be "/categories" and specify XPath query appropriately for every other attribute.
However, this works well for when I have just the one nested attribute. Is there a better way to do this if there is another nested attribute besides running an extract json for every nested attribute?

Re: [resolved] Reading JSON with nested arrays

Anonymous — Fri, 12 Dec 2014 09:36:03 GMT

The tMap component won't allow me to join this back to the main data, as I'm guessing it will only take input from two separate streams of data? There has to be a better way to essentially flatten a JSON with a nested array?

Take a look at this articleto know why it is not allowed. You have to redesign the job like:
main job:
tFileList--iterate--tRunJob
pass the current file path the child job.
child job:
tFileInputJson--main--tReplicate--main.....tHashOutput
--main--...tHashOutput
|
onsubjobok
|
tHashInput....tMap---
|
lookup
|
tHashInput
Shong