[resolved] tExtractJSONFields and List

[Anonymous]

Hi,
I'm new to Talend and using Talend Open Studio for Big Data.
I have a problem extracting datas from a JSON.
The JSON has a double list :
client : {
}]
I would like to have an output with as many rows as client like :
name_client : String
stuff : list
I am using tExtractJSONFIELDS, and have an input with the regular number of client but...I can only get the first couple (name_stuff, value_stuff). I can't get all the list.
Do you have any answer for the XPath request I should made to configure the line "stuff"?
Thanks.

[Anonymous]

Hi Largo
Please give us an real example with some data, and what are your expected result?
Shong

[Anonymous]

I'm having the same problem. I have an array in a JSON object returned from a tHttpRequest component and I'm unable to retrieve the values from inside it.
{"results":},
{"seq":3,"id":"_design/configuration","changes":},
{"seq":4,"id":"_design/country","changes":},
{"seq":5,"id":"56e675fc334b9cb6d3ba7c65c300028f","changes":},
{"seq":6,"id":"56e675fc334b9cb6d3ba7c65c3000fa2","changes":},
{"seq":7,"id":"56e675fc334b9cb6d3ba7c65c300150f","changes":},
{"seq":8,"id":"8ac4ff70c61b8f74b054a8c3510003ee","changes":},
{"seq":9,"id":"8ac4ff70c61b8f74b054a8c351000911","changes":},
{"seq":10,"id":"8ac4ff70c61b8f74b054a8c351001035","changes":},
{"seq":11,"id":"8ac4ff70c61b8f74b054a8c3510012ec","changes":},
{"seq":12,"id":"8ac4ff70c61b8f74b054a8c351001f20","changes":},
{"seq":13,"id":"8ac4ff70c61b8f74b054a8c35100212b","changes":},
{"seq":14,"id":"_design/key_alias","changes":},
{"seq":15,"id":"test","changes":},
{"seq":16,"id":"test2","changes":},
{"seq":17,"id":"test3","changes":},
{"seq":18,"id":"test4","changes":},
{"seq":19,"id":"test5","changes":},
{"seq":20,"id":"test6","changes":},
{"seq":21,"id":"test7","changes":},
{"seq":22,"id":"df05251c5b19404bab9c9cd0cc00220b","changes":},
{"seq":23,"id":"partner","changes":},
{"seq":24,"id":"partner2","changes":},
{"seq":25,"id":"partner3","changes":},
{"seq":27,"id":"test8","changes":},
{"seq":28,"id":"test9","changes":},
{"seq":29,"id":"test10","changes":},
{"seq":30,"id":"test11","changes":},
{"seq":31,"id":"test12","changes":},
{"seq":35,"id":"_design/auth","changes":},
{"seq":42,"id":"_design/amount","changes":},
{"seq":43,"id":"087bfebda8ff11f7651360f8e4000020","changes":}
],
"last_seq":43}

I'm trying to get one row for each index inside "results" and extract "rev" value from the first element in the "changes" node. I'm using the tExtractJSONFields component and I've tried all the combinations I can think of in the loop xpath and the mapping xpath queries. I would expect the queries to be something like:
Loop XPath query = "results
Mapping: Column = rev
XPath query = "changes/rev"
Any help or ideas anyone could provide would be appreciated.

Thanks,
Curtis Jones

[Anonymous · Accepted solution]

Hi Curtis
Set Loop Xpath query as "/results", and then you are able to extract values from the elements: seq, id, rev, for more details, please see my screenshots.
Shong

[Anonymous]

That's perfect. Thanks for your help.

[bi-dev-1]

The problem is not solved !
The question was about a loop inside a loop...

Well, as you said, we have to use the loop Xpath query to deal with the first loop.
We can create a column to contain the JSON code of the child loop. => don't forget to check the "get nodes" option
And this column can be transformed with a second tExtractJSONFileds.
The problem is that the component only extracts the first element of the child loop.

Example:
The input is:
{"contacts":},{"lastname":"simpson","firstname":"homer","passions":}]}
So we configure the tExtractJSONFileds as follows:
loop xpath query = "/contacts"
schema:
lastname = "./lastname"
firstname = "./firstname"
passions = "./passions" (get nodes !)
The result is:
simpson|bart|{passions":{"object":"skating","descr":"see the credits"}}
simpson|homer|{passions":{"object":"donuts","descr":"favourite food"}}
As you can see, the second passion is missing.

Do I miss something in the configuration ? or is there a bug in the component ?
Best regards,
Samuel

[Anonymous]

Hi Samuel
You can extract all values with only one tExtractJsonField component by setting the Loop Xpath query as "/contacts/passions", see my screenshot.
Shong

[Anonymous]

Yes, but you can't do it if you have more than one array to iterate over. You need to get the node and use another ExtractJSON component to do it.

[Anonymous]

Hello - I am running into a similar problem.  I'm using TOS DI 5.5.1.  I've tried Shong's suggestion but I am still getting "Cannot determine next state"  I am including the JSON which comes from a tRest component (and validates at jsonlint.com) and a screen shot of my tExtractJSONFields component.  Would you please help?
{
  "lists":
      ],
      "internalListId": 1
    },
    {
      "name": "HubSpot-HootSuite App",
      "metaData": {
        "size": 4,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1418738702960,
        "lastProcessingStateChangeAt": 1336673990262
      },       "dynamic": false,   &nb

To see the whole post, download it here
OriginalPost.pdf

[_AnonymousUser]

What I have to set in tExtractJSONFields to get following result

DATE_____  | CODE___  | REQUESTS
2016-08-20 | CODE-A20 | 45
2016-07-15 | CODE-B40 | 60

from following JSON

{
 "aaa": "bbb",
 "headers": ,
 "rows": ,
  
}