
Anonymous
Not applicable
2013-12-04
11:56 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
[resolved] tExtractJSONFields and List
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.
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.
869 Views
- « Previous Replies
-
- 1
- 2
- Next Replies »
1 Solution
Accepted Solutions

Anonymous
Not applicable
2013-12-16
10:04 PM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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
781 Views
10 Replies

Anonymous
Not applicable
2013-12-08
04:04 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Largo
Please give us an real example with some data, and what are your expected result?
Shong
Please give us an real example with some data, and what are your expected result?
Shong
781 Views

Anonymous
Not applicable
2013-12-13
04:39 PM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
{"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
781 Views

Anonymous
Not applicable
2013-12-16
10:04 PM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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
782 Views

Anonymous
Not applicable
2013-12-17
02:10 PM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That's perfect. Thanks for your help.
781 Views

Contributor
2014-01-21
10:19 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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
781 Views

Anonymous
Not applicable
2014-02-24
12:19 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
You can extract all values with only one tExtractJsonField component by setting the Loop Xpath query as "/contacts/passions", see my screenshot.
Shong
781 Views

Anonymous
Not applicable
2014-06-02
04:34 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
781 Views

Anonymous
Not applicable
2015-01-13
05:59 PM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
{
"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
781 Views

Specialist III
2016-09-30
03:14 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What I have to set in tExtractJSONFields to get following result
from following JSON
DATE_____ | CODE___ | REQUESTS
2016-08-20 | CODE-A20 | 45
2016-07-15 | CODE-B40 | 60
from following JSON
{
"aaa": "bbb",
"headers": ,
"rows": ,
}
781 Views

- « Previous Replies
-
- 1
- 2
- Next Replies »