topic Re: [resolved] tExtractJSONFields and List in Talend Studio

[resolved] tExtractJSONFields and List

Anonymous — Wed, 04 Dec 2013 16:56:26 GMT

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.

Re: [resolved] tExtractJSONFields and List

Anonymous — Sun, 08 Dec 2013 09:04:06 GMT

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

Re: [resolved] tExtractJSONFields and List

Anonymous — Fri, 13 Dec 2013 21:39:01 GMT

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

Re: [resolved] tExtractJSONFields and List

Anonymous — Tue, 17 Dec 2013 03:04:00 GMT

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

Re: [resolved] tExtractJSONFields and List

Anonymous — Tue, 17 Dec 2013 19:10:20 GMT

That's perfect. Thanks for your help.

Re: [resolved] tExtractJSONFields and List

bi-dev-1 — Tue, 21 Jan 2014 15:19:40 GMT

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

Re: [resolved] tExtractJSONFields and List

Anonymous — Mon, 24 Feb 2014 05:19:19 GMT

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

Re: [resolved] tExtractJSONFields and List

Anonymous — Mon, 02 Jun 2014 08:34:18 GMT

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.

Re: [resolved] tExtractJSONFields and List

Anonymous — Tue, 13 Jan 2015 22:59:05 GMT

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

Re: [resolved] tExtractJSONFields and List

_AnonymousUser — Fri, 30 Sep 2016 19:14:43 GMT

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": ,
  
}

Re: [resolved] tExtractJSONFields and List

Anonymous — Sun, 08 Jul 2018 14:17:34 GMT

Hey, are able to get the result what you want ???