Filter oData with array of custom attribute ( $expand ) and use $filter for a specific attribute-CodePudding

With this structure of the oData v2 result from a GetEntitySet (JSON Format)

{
d: {
__count: "3215",
result : [
 {
   City    : 'NewYork',
   Country : 'USA',
   CustomAttributeData : {
     results : [ 
      {   Name: "custom1",
          Value: "20220707"
      },
      {   Name: "custom2",
          Value: "20220710"
      },
      {   Name: "custom3",
          Value: "20220713"
      }
    ]
  }
 },
 {
   City    : 'Rome',
   Country : 'ITALY',
   CustomAttributeData : {
     results : [ 
      {   Name: "custom1",
          Value: "20220702"
      },
      {   Name: "custom2",
          Value: "20220710"
      },
      {   Name: "custom3",
          Value: "20220710"
      }
    ]
  }
 },
 {
   City    : 'Tokyo',
   Country : 'JAPAN',
   CustomAttributeData : {
     results : [ 
      {   Name: "custom1",
          Value: "20220710"
      },
      {   Name: "custom2",
          Value: "20220711"
      },
      {   Name: "custom3",
          Value: "20220710"
      }
    ]
  }
 }
],
}
}
....

]

I want to filter all the cities with the CustomAttributeData custom2=20220710 (all but not Tokyo). What is the correct uri ?

https://xxxxxx?$expand=CustomAttributeData$filter=CustomAttributeData/Value eq '20220710'

This one is of course wrong because consider Tokyo too.

Someone can help me?

CodePudding user response：

You can filter out Tokyo by using ne operator in $filter query.

$filter=City ne 'Tokyo'

Added to your query

https://xxxxxx?$expand=CustomAttributeData&$filter=City ne 'Tokyo' and CustomAttributeData/Value eq '20220710'

CodePudding user response：

With OData V2, it's not possible. You'll need to use the any lambda operator* for your filter which is available only in OData V4.

* See also this SAP Developer video section which explains any and all operators for OData V4 filters.