Consuming REST API and extracting JSON Fields

Highlighted
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
Eight 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.

View solution in original post


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
}

 

Eight 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.

View solution in original post

Five Stars

Re: Consuming REST API and extracting JSON Fields

That seems to have got me a further, Thanks !


2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog