Consuming REST API and extracting JSON Fields

Five Stars

Consuming REST API and extracting JSON Fields

I'm pretty new to Talend and have run into a problem I need help with. I have a REST API that I need to connect to and extract the JSON fields and push them to a JSON output file. I am able to connect to the service using the tRestClient. The issue I'm having is trying to extract the fields. I though the tExtractJSONFields would allow me to extract the fields but haven't much luck with it. I know i can manually map the fields to an output but I'd rather not cause eventually I'll be connecting to multiple services and will need to be able to get the fields automatically. I'v attached a pic of my current job. Thank you in advance for your assistance.


Accepted Solutions
Seven Stars

Re: Consuming REST API and extracting JSON Fields

Try using read by: JsonPath instead of XPath.  Depending on which fields you wish to extract will determine the Loop Jsonpath query.  Typically I use "$" as the base element, then add schema that represent every field I wish to extract.  The Json query field in each schema element represents the JSONPath Query relative to the Loop Jsonpath query.  

 

2018-03-08_10-38-45.png

If the JSON is complex with many nested Objects/Arrays, it may be beneficial to use multiple tExtractJSONField components consecutively.


All Replies
Ten Stars

Re: Consuming REST API and extracting JSON Fields

Could you upload your json response from your call ... 

And Is it the xpath query you find difficult to extract field?

https://help.talend.com/reader/g8zdjVE7fWNUh3u4ztO6Dw/Je6AV_TMnYHC_MpEtaMBaA

 

 

Five Stars

Re: Consuming REST API and extracting JSON Fields

Attached is the response I receive and yes I was struggling with whether to use the JsonPath or the XPath

 

{
"eventAttempted": "TryRead",
"output": [
{
"id": "5a6be78asdfe8cab22a",
"shortName": "Loc Header 36",
"businessUnitViewOnly": [],
"locations": [],
"modified": "0001-01-01T00:00:00Z",
"created": "0001-01-01T00:00:00Z"
},
{
"id": "5a6bwerqe8e8cab22d",
"shortName": "Location Header 39",
"ownerBusinessUnit": { "id": "2" },
"businessUnitViewOnly": [ { "id": "1" } ],
"locations": [],
"modifiedBy": "John Stub",
"modified": "2018-03-07T21:41:42.768Z",
"created": "0001-01-01T00:00:00Z"
},
{
"id": "5a79werewebd40",
"shortName": "Test LocHeader 01f9bb52qwpoerqlkc7978860a8b",
"businessUnitViewOnly": [],
"locations": [],
"modified": "0001-01-01T00:00:00Z",
"created": "0001-01-01T00:00:00Z"
},
{
"id": "5a79ec5werasdftre81e9ce",
"shortName": "Test LocHeader 6cbcc29d-pouoreruqwre-28dc34a3678f",
"businessUnitViewOnly": [],
"locations": [],
"modified": "0001-01-01T00:00:00Z",
"created": "0001-01-01T00:00:00Z"
}
],
"qtyOverall": 4,
"qtyReturned": 4,
"successful": true
}

 

Seven Stars

Re: Consuming REST API and extracting JSON Fields

Try using read by: JsonPath instead of XPath.  Depending on which fields you wish to extract will determine the Loop Jsonpath query.  Typically I use "$" as the base element, then add schema that represent every field I wish to extract.  The Json query field in each schema element represents the JSONPath Query relative to the Loop Jsonpath query.  

 

2018-03-08_10-38-45.png

If the JSON is complex with many nested Objects/Arrays, it may be beneficial to use multiple tExtractJSONField components consecutively.

Five Stars

Re: Consuming REST API and extracting JSON Fields

That seems to have got me a further, Thanks !