How to filter data in the api url call?

[rp2018]

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"

      }

     }

   }

  ]

}

[anselmopeixoto]

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

[Anonymous · Accepted solution]

Hi

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

[rp2018]

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+"'))]"