topic How to filter data in the api url call? in Talend Studio

How to filter data in the api url call?

rp2018 — Fri, 15 Nov 2024 21:36:59 GMT

Have this url "https://test.net/test.pl/emp/list" to connect to the end system but would like to only extract rows where created_on or updated_on is >= today's date.

How do you accomplish this in thttprequest component?

This is the json structure outputted.

{

"list":"API: Action - List",

"test":[

{

"fields":{

"address1":{

"formatted_value":null,

"label":"Address",

"maxlength":255,

"required":false,

"type":"text",

"unique":false,

"updatable":true,

"value":null

"created_on" : {

"formatted_value" : "2022-11-05",

"label" : "Created On",

"required" : false,

"type" : "date",

"unique" : false,

"updatable" : false,

"value" : "2022-11-05"

}

"updated_on" : {

"formatted_value" : "2023-07-06",

"label" : "Updated On",

"required" : false,

"type" : "date",

"unique" : false,

"updatable" : false,

"value" : "2023-07-06"

}

]

}

Re: How to filter data in the api url call?

anselmopeixoto — Thu, 06 Jul 2023 18:13:32 GMT

Hi @Ranjan Patel

You could use tExtractJsonField to map JSON elements to schema columns. Then, using a tMap or a tFilterRow you can use the following expression the filter the output rows:

TalendDate.compareDate(row1.created_on, TalendDate.getCurrentDate()) > 0 || TalendDate.compareDate(row1.updated_on, TalendDate.getCurrentDate()) > 0

Re: How to filter data in the api url call?

Anonymous — Fri, 07 Jul 2023 03:26:22 GMT

You can filter the JSON data using SONPath expressions on tExtractJsonField, please refer to this KB article

https://community.talend.com/s/article/Using-JSONPath-to-Filter-JSON-data

Regards

Shong

Re: How to filter data in the api url call?

rp2018 — Fri, 14 Jul 2023 16:16:18 GMT

Thanks Shong for the URL. Used the URL info and was able to filter the data.

Here is the solution.

Create context variable called CurrentDate and set the datatype to string.

In Java component set the context.CurrentDate = TalendDate.formatDate("yyyy-MM-dd",new Date());

tJava-->On Component OK-->tHttpRequest-->tExtractJsonField-->tLogRow

Add the below query to the Loop JsonPath query in tExtractJsonField component:

"$.test[?((@.fields.updated_on.value>='"+context.CurrentDate+"'||@.fields.created_on.value>='"+context.CurrentDate+"'))]"