One Star

[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.
1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

Re: [resolved] tExtractJSONFields and List

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
9 REPLIES
Community Manager

Re: [resolved] tExtractJSONFields and List

Hi Largo
Please give us an real example with some data, and what are your expected result?
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] tExtractJSONFields and List

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
Community Manager

Re: [resolved] tExtractJSONFields and List

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] tExtractJSONFields and List

That's perfect. Thanks for your help.

Re: [resolved] tExtractJSONFields and List

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
Community Manager

Re: [resolved] tExtractJSONFields and List

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] tExtractJSONFields and List

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.
One Star

Re: [resolved] tExtractJSONFields and List

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,
      "updatedAt": 1336673951299,
      "listId": 2,
      "portalId": 62515,
      "createdAt": 1336673951299,
      "filters":
      ],
      "internalListId": 2
    },
    {
      "name": "staticTest",
      "metaData": {
        "size": 5,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1409043908127,
        "lastProcessingStateChangeAt": 1344891893390
      },
      "listType": "STATIC",
      "dynamic": false,
      "updatedAt": 1344891893390,
      "listId": 3,
      "portalId": 62515,
      "createdAt": 1344891893390,
      "filters": ,
      "internalListId": 5
    },
    {
      "name": "Workface Leads",
      "metaData": {
        "size": 1,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1412075553438,
        "lastProcessingStateChangeAt": 1345057450679
      },
      "listType": "STATIC",
      "dynamic": false,
      "updatedAt": 1345057450679,
      "listId": 4,
      "portalId": 62515,
      "createdAt": 1345057450679,
      "filters": ,
      "internalListId": 6
    },
    {
      "name": "stuff",
      "metaData": {
        "size": 0,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1356047577025,
        "lastProcessingStateChangeAt": 1352457865767
      },
      "listType": "DYNAMIC",
      "dynamic": true,
      "updatedAt": 1352457795448,
      "listId": 5,
      "portalId": 62515,
      "createdAt": 1345150315056,
      "filters":
      ],
      "internalListId": 111
    },
    {
      "name": "test1.csv",
      "metaData": {
        "size": 1134,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1412965644007,
        "lastProcessingStateChangeAt": 1412965644048
      },
      "listType": "STATIC",
      "dynamic": false,
      "updatedAt": 1348730165681,
      "deleted": false,
      "listId": 106,
      "portalId": 62515,
      "createdAt": 1348730165681,
      "filters": ,
      "internalListId": 108
    },
    {
      "name": "VP From LS",
      "metaData": {
        "size": 0,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1379278698150,
        "lastProcessingStateChangeAt": 1353112579422
      },
      "listType": "DYNAMIC",
      "dynamic": true,
      "updatedAt": 1353448496108,
      "listId": 123,
      "portalId": 62515,
      "createdAt": 1353112478149,
      "filters":
      ],
      "internalListId": 128
    },
    {
      "name": "VP Self Signed Up",
      "metaData": {
        "size": 0,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1379278698143,
        "lastProcessingStateChangeAt": 1353435356844
      },
      "listType": "DYNAMIC",
      "dynamic": true,
      "updatedAt": 1353435268894,
      "listId": 124,
      "portalId": 62515,
      "createdAt": 1353435268894,
      "filters":
      ],
      "internalListId": 129
    },
    {
      "name": "VP User Created By Admin",
      "metaData": {
        "size": 0,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1379278604544,
        "lastProcessingStateChangeAt": 1353448911950
      },
      "listType": "DYNAMIC",
      "dynamic": true,
      "updatedAt": 1353448679894,
      "listId": 125,
      "portalId": 62515,
      "createdAt": 1353435309254,
      "filters":
      ],
      "internalListId": 137
    },
    {
      "name": "VP Test Users",
      "metaData": {
        "size": 0,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1379278698138,
        "lastProcessingStateChangeAt": 1354183186104
      },
      "listType": "DYNAMIC",
      "dynamic": true,
      "updatedAt": 1353445432440,
      "listId": 126,
      "portalId": 62515,
      "createdAt": 1353445432440,
      "filters":
      ],
      "internalListId": 141
    },
    {
      "name": "Enter Photo Contest",
      "metaData": {
        "size": 0,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1379278879425,
        "lastProcessingStateChangeAt": 1353956891525
      },
      "listType": "STATIC",
      "dynamic": false,
      "updatedAt": 1353956891525,
      "listId": 127,
      "portalId": 62515,
      "createdAt": 1353956891525,
      "filters": ,
      "internalListId": 139
    },
    {
      "name": "Win Golf Stand Bag",
      "metaData": {
        "size": 0,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1379278348398,
        "lastProcessingStateChangeAt": 1354047260466
      },
      "listType": "STATIC",
      "dynamic": false,
      "updatedAt": 1354047260466,
      "listId": 128,
      "portalId": 62515,
      "createdAt": 1354047260466,
      "filters": ,
      "internalListId": 140
    },
    {
      "name": "US Person",
      "metaData": {
        "size": 0,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1358762885860,
        "lastProcessingStateChangeAt": 1358762885859
      },
      "listType": "DYNAMIC",
      "dynamic": true,
      "updatedAt": 1358762858893,
      "listId": 132,
      "portalId": 62515,
      "createdAt": 1358415795285,
      "filters":
      ],
      "internalListId": 154
    },
    {
      "name": "Klpz",
      "metaData": {
        "size": 25,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1416616390083,
        "lastProcessingStateChangeAt": 1379965841604
      },
      "listType": "DYNAMIC",
      "dynamic": true,
      "updatedAt": 1379965836651,
      "listId": 137,
      "portalId": 62515,
      "createdAt": 1358866329333,
      "filters":
      ],
      "deleteable": true,
      "internalListId": 273
    },
    {
      "name": "pooja.arora@utsavfashion.biz",
      "metaData": {
        "size": 0,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1409867632129,
        "lastProcessingStateChangeAt": 1360391773728
      },
      "listType": "STATIC",
      "dynamic": false,
      "updatedAt": 1360391773728,
      "listId": 140,
      "portalId": 62515,
      "createdAt": 1360391773728,
      "filters": ,
      "internalListId": 158
    },
    {
      "name": "tweeters",
      "metaData": {
        "size": 25,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1416868964666,
        "lastProcessingStateChangeAt": 1360714782028
      },
      "listType": "STATIC",
      "dynamic": false,
      "updatedAt": 1360714782028,
      "listId": 145,
      "portalId": 62515,
      "createdAt": 1360714782028,
      "filters": ,
      "internalListId": 163
    },
    {
      "name": "tweeters2",
      "metaData": {
        "size": 0,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 0,
        "lastProcessingStateChangeAt": 1360714845104
      },
      "listType": "STATIC",
      "dynamic": false,
      "updatedAt": 1360714845104,
      "listId": 146,
      "portalId": 62515,
      "createdAt": 1360714845104,
      "filters": ,
      "internalListId": 164
    },
    {
      "name": "tweeters3",
      "metaData": {
        "size": 2,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 1399965262559,
        "lastProcessingStateChangeAt": 1360714853388
      },
      "listType": "STATIC",
      "dynamic": false,
      "updatedAt": 1360714853388,
      "listId": 147,
      "portalId": 62515,
      "createdAt": 1360714853388,
      "filters": ,
      "internalListId": 165
    },
    {
      "name": "tweetersasdf",
      "metaData": {
        "size": 0,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 0,
        "lastProcessingStateChangeAt": 1360715752360
      },
      "listType": "STATIC",
      "dynamic": false,
      "updatedAt": 1360715752360,
      "listId": 148,
      "portalId": 62515,
      "createdAt": 1360715752360,
      "filters": ,
      "internalListId": 166
    },
    {
      "name": "tweeterszzzzz",
      "metaData": {
        "size": 0,
        "error": "",
        "processing": "DONE",
        "lastSizeChangeAt": 0,
        "lastProcessingStateChangeAt": 1360718454238
      },
      "listType": "STATIC",
      "dynamic": false,
      "updatedAt": 1360718454238,
      "listId": 149,
      "portalId": 62515,
      "createdAt": 1360718454238,
      "filters": ,
      "internalListId": 167
    }
  ],
  "offset": 20,
  "has-more": true
}
One Star

Re: [resolved] tExtractJSONFields and List

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