topic Re: Consuming REST API and extracting JSON Fields in Talend Studio

Consuming REST API and extracting JSON Fields

Anonymous — Wed, 07 Mar 2018 16:15:47 GMT

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.

Re: Consuming REST API and extracting JSON Fields

Jesperrekuh — Wed, 07 Mar 2018 23:26:21 GMT

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

Re: Consuming REST API and extracting JSON Fields

Anonymous — Thu, 08 Mar 2018 15:25:26 GMT

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
}

Re: Consuming REST API and extracting JSON Fields

Anonymous — Thu, 08 Mar 2018 15:40:26 GMT

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.

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

Re: Consuming REST API and extracting JSON Fields

Anonymous — Thu, 08 Mar 2018 16:18:55 GMT

That seems to have got me a further, Thanks !