Integration Studio - Filtering a JSON (SMAX 24.1)

 In Integration Studio I'm having a syntax issue trying to filter a json to extract an element. In the sample json below, abbreviated for simplicity, I need to extract the endDate where the entitlementType=='INITIAL' && serviceLevelCode=='ND'.

I had thought this would work (and tried quite a few other variations): filter(json_parser(response.data,'$.result.entitlements'),'item.entitlementType=="EXTENDED" && item.ServiceLevelCode=="ND"','{"EndDt":item.endDate}',0)

But it does not extract the element.  Other, unfiltered elements are coming out fine.  What's wrong with my syntax? 

Thanks in advance, 

Lyn

  "code": 0,
  "originalCode": 200,
  "localChannel": "47",
  "countryCode":"US",

  "result": [
    {
      "id": 2137399848,
      "entitlements": [
        {

          "itemNumber": "1gj",
          "endDate": "2025-07-24T04:59:59.000001Z",
          "entitlementType": "INITIAL",
          "serviceLevelCode": "ND"
         },
        {
           "itemNumber": "2rb",
           "endDate": "2025-07-24T04:59:59.000001Z",
           "entitlementType": "INITIAL",
           "serviceLevelCode": "RR"
        },
        {
          "itemNumber": "3a",
          "endDate": "2028-07-24T04:59:59.000001Z",
          "entitlementType": "EXTENDED",
          "serviceLevelCode": "ND"
        }
      ]
    }
  ]
}

Parents
  • Verified Answer

    +1  

    Hi  

    According to the JSON item you provided

    the result and entitlements both are arrays

    Which element of the result array to use is not specified

    filter(json_parser(response.data,'$.result.entitlements'),'item.entitlementType=="EXTENDED" && item.ServiceLevelCode=="ND"','{"EndDt":item.endDate}',0)

    Could you please replace it with the below text and test it?

    filter(json_parser(response.data,'$.result[0].entitlements'),'item.entitlementType=="EXTENDED" && item.ServiceLevelCode=="ND"','{"EndDt":item.endDate}',0)

  • 0 in reply to   

    Thanks very much!  That was really really close... 

    filter(json_parser(response.data,'$.result[0].entitlements'),'item.entitlementType=="EXTENDED" && item.serviceLevelCode=="ND"','{"EndDt":item.endDate}',0)

    returned execution details:

    {"expression":"filter(json_parser(response.data,'$.result[0].entitlements'),'item.entitlementType==\"EXTENDED\" && item.serviceLevelCode==\"ND\"','{\"EndDt\":item.endDate}',0);","result":"null"}

    I then tried changing result[0] to [0]

    filter(json_parser(response.data,'$.result[0].entitlements'),'item.entitlementType=="EXTENDED" && item.serviceLevelCode=="ND"','{"EndDt":item.endDate}',0)

    returned execution details: 

    {"expression":"filter(json_parser(response.data,'$.[0].entitlements'),'item.entitlementType==\"EXTENDED\" && item.serviceLevelCode==\"ND\"','{\"EndDt\":item.endDate}',0);","result":[{"EndDt":"2027-10-03T04:59:59.000001Z"}]}

    Great!!!!  Thanks very much! 

    One follow on question, if you care to answer it.....how would I get it to just respond with a result of "2027-10-03T04:59:59.000001Z" (no array, no header.) I tried to remove the EndDt: from the expression but that was not successful.

    Regards,

    Lyn

  • Verified Answer

    +1   in reply to 

    Hi  

    Here, the entities is an array and this loaded into field Arr

    field Number is used to get the length of the Array

    So I'm loading the first element of the array Arr into fields Brr and Crr

    The value in fields Brr and Crr is same

    You can use either of these methods to get the value outside of the Array

    I couldn't find a direct method to extract the value directly while using filter method. So this is an alternative

    We can use the values of other fields to recreate a new field.

    If you have loaded your endDt into field result

    then create another filed in the output object named as date and set the value as if(Output?.result != []) (Output.result[0]) else {null}

    You can set null or any other default value for else condition based on your requirement

    This above method is a work around by creating another field. I will check if there is any direct method we can use outside filter method to achieve this.

  • 0 in reply to   

    Thaniks!  I ended up doing an additional json_parser (and parse_date(json_parser(...) ) further down in the scenario to grab the date, like  your Arr example.   I'll probably try and combine all  that into one.   Again, thanks so much for your help, that syntax was just escaping me; really appreciate it. 

Reply
  • 0 in reply to   

    Thaniks!  I ended up doing an additional json_parser (and parse_date(json_parser(...) ) further down in the scenario to grab the date, like  your Arr example.   I'll probably try and combine all  that into one.   Again, thanks so much for your help, that syntax was just escaping me; really appreciate it. 

Children
No Data