Search code examples
odata

Filter field by length with OData

I'm in the strange situation that I have some numbers stored as text in a DB. Not being very familiar with OData, I'm trying to write a query that handles those strings as actual numbers.

$filter=(((length(Document/DocumentSet/ReferenceNumbers) ge 3) and (Document/DocumentSet/ReferenceNumbers gt '100')))

is the best I could come up with, but that gives me an ODataException

The argument for an invocation of a function with name 'length' is not a single value. All arguments for this function must be single values.

Or if I try to use (I'm still trying to figure out which one is right, I really don't know OData):

$filter=(((length(Document/DocumentSet/ReferenceNumbers/Number) ge 3) and (Document/DocumentSet/ReferenceNumbers/Number gt '100')))

then

The parent value for a property access of a property 'Number' is not a single value. Property access can only be applied to a single value.

The question is: is there any way to filter something with OData by length?

Solution

  • From what you posted, I understood that ReferenceNumbers property is an array, because errors tell about not a single value.

    For filtering on arrays, you need to use lambda operators (5.1.1.10 in the official OData documentation):

    • any - when at least one entry has to match the conditions
    • all - when all the array entries have to match them

    And then length with gt should work for you. Example request - all the ReferenceNumbers should be greater than 100:

    root/set?$filter=Document/DocumentSet/ReferenceNumbers/any(refNum:refNum gt '100')