[resolved] Reading JSON with nested arrays

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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!

[Anonymous]

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

Best regards
Shong

[Anonymous]

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?

[Anonymous]

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?

[Anonymous]

Hi

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