Filter rows using OData

Cikk
07/11/2024

Use the $filter query option to filter a collection of resources.

Dataverse evaluates each resource in the collection using the expression set for $filter. Only records where the expression evaluates to true are returned in the response. Records aren't returned if the expression evaluates to false or null, or if the user doesn't have read access to the record.

The following table describes the operators and functions you can use in $filter expressions.

	Description	More information
Comparison operators	Use the `eq`,`ne`,`gt`,`ge`,`lt`, and `le` operators to compare a property and a value.	Comparison operators
Logical operators	Use `and`, `or`, and `not` to create more complex expressions.	Logical operators
Grouping operators	Use parentheses: `()`, to specify the precedence to evaluate a complex expression.	Grouping operators
OData query functions	Evaluate string values using `contains`, `endswith`, and `startswith` functions.	Use OData query functions
Dataverse query functions	Use more than 60 specialized functions designed for business applications.	Dataverse query functions
Lambda expressions	Create expressions based on values of related collections.	Filter using values of related collections

Comparison operators

The following table describes the operators you can use to compare a property and a value.

Operator	Description	Example
`eq`	Equal	`$filter=revenue eq 100000`
`ne`	Not Equal	`$filter=revenue ne 100000`
`gt`	Greater than	`$filter=revenue gt 100000`
`ge`	Greater than or equal	`$filter=revenue ge 100000`
`lt`	Less than	`$filter=revenue lt 100000`
`le`	Less than or equal	`$filter=revenue le 100000`

Column comparison

You can use comparison operators to compare property values in the same row. Only comparison operators can be used to compare values in the same row, and the column types must match. For example, the following query returns any contacts where firstname equals lastname:

GET [Organization URI]/api/data/v9.2/contacts?$select=fullname&$filter=firstname eq lastname

Logical operators

The following table describes the logical operators you can use to create more complex expressions.

Operator	Description	Example
`and`	Logical and	`$filter=revenue lt 100000 and revenue gt 2000`
`or`	Logical or	`$filter=contains(name,'(sample)') or contains(name,'test')`
`not`	Logical negation	`$filter=not contains(name,'sample')`

Grouping operators

Use parentheses () with logical operators to specify the precedence to evaluate a complex expression; for example:

$filter=(contains(name,'sample') or contains(name,'test')) and revenue gt 5000

Dataverse query functions

Use more than 60 specialized functions designed for business applications. These functions provide special capabilities, as described in the following table.

Group	Functions
Dates	InFiscalPeriod, InFiscalPeriodAndYear, InFiscalYear, InOrAfterFiscalPeriodAndYear, InOrBeforeFiscalPeriodAndYear, Last7Days, LastFiscalPeriod, LastFiscalYear, LastMonth, LastWeek, LastXDays, LastXFiscalPeriods, LastXFiscalYears, LastXHours, LastXMonths, LastXWeeks, LastXYears, LastYear, Next7Days, NextFiscalPeriod, NextFiscalYear, NextMonth, NextWeek, NextXDays, NextXFiscalPeriods, NextXFiscalYears, NextXHours, NextXMonths, NextXWeeks, NextXYears, NextYear, OlderThanXDays, OlderThanXHours, OlderThanXMinutes, OlderThanXMonths, OlderThanXWeeks, OlderThanXYears, On, OnOrAfter, OnOrBefore, ThisFiscalPeriod, ThisFiscalYear, ThisMonth, ThisWeek, ThisYear, Today, Tomorrow, Yesterday
Id Values	EqualBusinessId, EqualUserId, NotEqualBusinessId, NotEqualUserId
Hierarchy	Above, AboveOrEqual, EqualUserOrUserHierarchy, EqualUserOrUserHierarchyAndTeams, EqualUserOrUserTeams, EqualUserTeams, NotUnder, Under, UnderOrEqual More information: Query hierarchical data
Choices columns	ContainValues, DoesNotContainValues More information: Query data from choices
Between	Between, NotBetween
In	In, NotIn
Language	EqualUserLanguage

Note

The Contains function is for use with columns that have full-text indexing. Only the Dynamics 365 KBArticle (article) table has columns that have full-text indexing. Use the OData contains function instead.

The Web API Query Function Reference has the complete list. Each article provides a syntax example you can copy.

You must use the function's fully qualified name and append the Service namespace (Microsoft.Dynamics.CRM) to the name of the function.

Each function has a PropertyName parameter that specifies the property to be evaluated. The function may have more parameters, such as PropertyValue, PropertyValues, or PropertyValue1 and PropertyValue2. When these parameters exist, you must supply a value, or values, to compare to the PropertyName parameter.

The following example shows uses the Between function to search for accounts with between 5 and 2,000 employees.

GET [Organization URI]/api/data/v9.2/accounts?$select=name,numberofemployees
&$filter=Microsoft.Dynamics.CRM.Between(PropertyName='numberofemployees',PropertyValues=["5","2000"])

Filter using string values

Keep the following points in mind when you filter on string values:

All filters using string values are case insensitive.
You must URL encode special characters in filter criteria. More information: URL encode special characters
You may use wildcard characters, but avoid using them incorrectly. More information: Use wildcard characters
You can use OData query functions: contains, startswith, and endswith. More information: Use OData query functions
You must manage single quotes when you use filters that accept an array of string values. More information: Manage single quotes

URL encode special characters

If the string you are using as a value in a filter function includes a special character, you need to URL encode it. For example, if you use this function: contains(name,'+123'), it will not work because + is a character that can't be included in a URL. If you URL encode the string, it will become contains(name,'%2B123') and you will get results where the column value contains +123.

The following table shows the URL encoded values for common special characters.

Special character	URL encoded character
`$`	`%24`
`&`	`%26`
`+`	`%2B`
`,`	`%2C`
`/`	`%2F`
`:`	`%3A`
`;`	`%3B`
`=`	`%3D`
`?`	`%3F`
`@`	`%40`

Use wildcard characters

When composing filters using strings, you can apply the following wildcard characters:

Characters	Description	T-SQL documentation and examples
`%`	Matches any string of zero or more characters. This wildcard character can be used as either a prefix or a suffix.	Percent character (Wildcard - Character(s) to Match) (Transact-SQL)
`_`	Use the underscore character to match any single character in a string comparison operation that involves pattern matching.	_ (Wildcard - Match One Character) (Transact-SQL)
`[]`	Matches any single character within the specified range or set that is specified between brackets.	[ ] (Wildcard - Character(s) to Match) (Transact-SQL)
`[^]`	Matches any single character that isn't within the range or set specified between the square brackets.	[^] (Wildcard - Character(s) Not to Match) (Transact-SQL)

More information: Use wildcard characters in conditions for string values

Leading wildcards not supported

It's important not to use leading wild cards because they aren't supported. Queries that use these anti-patterns introduce performance problems because the queries can't be optimized. Here are some examples of leading wildcards:

startswith(name,'%value')
endswith(name,'value%')

Learn more about errors returned when leading wildcards are used

Use OData query functions

The following table describes the OData query functions you can use to filter on string values:

Function	Example
`contains`	`$filter=contains(name,'(sample)')`
`endswith`	`$filter=endswith(name,'Inc.')`
`startswith`	`$filter=startswith(name,'a')`

You can use these functions with the logical operator not to negate the result.

Manage single quotes

Some filters accept an array of string values, such as the In Query function. When you specify values in these filters that contain single quote, or apostrophe, characters, such as O'Brian or Men's clothes, you must use double quotes around the values; for example:

GET [Organization URI]/api/data/v9.2/contacts?$select=fullname
&$filter=Microsoft.Dynamics.CRM.In(PropertyName=@p1,PropertyValues=@p2)
&@p1='lastname'
&@p2=["OBrian","OBryan","O'Brian","O'Bryan"]

If you don't, you get the following error: Invalid JSON. A comma character ',' was expected in scope 'Array'. Every two elements in an array and properties of an object must be separated by commas.

If the filter is for a single value, replace the single quote character with two consecutive single quote characters; for example:

GET [Organization URI]/api/data/v9.2/contacts?$select=fullname
&$filter=lastname eq 'O''Bryan'

If you don't, you get an error like this: There is an unterminated literal at position 21 in 'lastname eq 'O'Bryan''.

You can filter rows returned based on values in related tables. How you filter depends on the type of relationship.

Filter on lookup property

For one-to-many relationships, a filtered collection returns the same results as using an eq $filter on the Lookup property for the relationship. For example, this filtered collection:

GET [Organization URI]/api/data/v9.2/systemusers(<systemuserid value>)/user_accounts?$select=name

Is the same as this filter on a lookup property.

GET [Organization URI]/api/data/v9.2/accounts?$filter=_owninguser_value eq <systemuserid value>&$select=name

Filter using lookup column property values

You can filter based on values in single-valued navigation properties that represent lookup columns. Use this pattern:

<single-valued navigation property>/<property name>

The following example returns account records based on the value of the primarycontactid/fullname column:

Request:

GET [Organization URI]/api/data/v9.2/accounts?$filter=primarycontactid/fullname eq 'Susanna Stubberod (sample)'
&$select=name,_primarycontactid_value
Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"

Response:

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0
Preference-Applied: odata.include-annotations="OData.Community.Display.V1.FormattedValue"

{
    "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,_primarycontactid_value)",
    "value": [
        {
            "@odata.etag": "W/\"81359849\"",
            "name": "Litware, Inc. (sample)",
            "_primarycontactid_value@OData.Community.Display.V1.FormattedValue": "Susanna Stubberod (sample)",
            "_primarycontactid_value": "70bf4d48-34cb-ed11-b596-0022481d68cd",
            "accountid": "78914942-34cb-ed11-b596-0022481d68cd"
        }
    ]
}

You can also compare values further up the hierarchy of single-valued navigation properties.

The following example returns the first account where the contact record represents the primarycontactid, where 'System Administrator' created the record, using primarycontactid/createdby/fullname in the $filter.

Request:

GET [Organization URI]/api/data/v9.2/accounts?$filter=primarycontactid/createdby/fullname eq 'System Administrator'
&$select=name,_primarycontactid_value
&$expand=primarycontactid(
$select=fullname,_createdby_value;
$expand=createdby($select=fullname))
&$top=1
Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"

Response:

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0
Preference-Applied: odata.include-annotations="OData.Community.Display.V1.FormattedValue"

{
    "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,_primarycontactid_value,primarycontactid(fullname,_createdby_value,createdby(fullname)))",
    "value": [
        {
            "@odata.etag": "W/\"81359849\"",
            "name": "Litware, Inc. (sample)",
            "_primarycontactid_value@OData.Community.Display.V1.FormattedValue": "Susanna Stubberod (sample)",
            "_primarycontactid_value": "70bf4d48-34cb-ed11-b596-0022481d68cd",
            "accountid": "78914942-34cb-ed11-b596-0022481d68cd",
            "primarycontactid": {
                "fullname": "Susanna Stubberod (sample)",
                "_createdby_value@OData.Community.Display.V1.FormattedValue": "System Administrator",
                "_createdby_value": "4026be43-6b69-e111-8f65-78e7d1620f5e",
                "contactid": "70bf4d48-34cb-ed11-b596-0022481d68cd",
                "createdby": {
                    "fullname": "System Administrator",
                    "systemuserid": "4026be43-6b69-e111-8f65-78e7d1620f5e",
                    "ownerid": "4026be43-6b69-e111-8f65-78e7d1620f5e"
                }
            }
        }
    ]
}

Use the Lambda operators any and all to evaluate values in a collection to filter the results.

any: Returns true if the expression applied is true for any member of the collection; otherwise, it returns false.
- The any operator without an argument returns true if the collection isn't empty.
all: Returns true if the expression applied is true for all members of the collection; otherwise, it returns false.

The syntax looks like this:

<collection>/[any | all](o:<expression to evaluate>)

In this case, o is the variable that represents items in the collection. The convention is to use the first letter of the type. In the expression, use o/<property or collection name> to refer to a property or collection of a given item.

You can include conditions on multiple collection-valued navigation properties and nested collections. You can't include conditions on collection-valued navigation properties that are nested in a lookup navigation property. For example, $filter=primarycontactid/new_contact_account/any(a:a/accountid eq '{GUID}') isn't supported.

More information: Lambda Operators at odata.org

Lambda operator examples

The following example retrieves all account records that have at least one email with "sometext" in the subject:

GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$filter=Account_Emails/any(e:contains(e/subject,'sometext'))
Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0

The following example retrieves all account records that have all associated tasks closed:

GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$filter=Account_Tasks/all(t:t/statecode eq 1)
Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0

The following example retrieves all account records that have at least one email with "sometext" in the subject and whose state code is active:

GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$filter=Account_Emails/any(e:contains(e/subject,'sometext') and 
e/statecode eq 0)
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0

The following example creates a nested query using any and all operators:

GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$filter=(contact_customer_accounts/any(c:c/jobtitle eq 'jobtitle' and 
c/opportunity_customer_contacts/any(o:o/description ne 'N/A'))) and 
endswith(name,'Inc.')
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0

Condition limits

You can include no more than 500 total conditions in a query. Otherwise, you see this error:

Name: TooManyConditionsInQuery
Code: 0x8004430C
Number: -2147204340
Message: Number of conditions in query exceeded maximum limit.

You need to reduce the number of conditions to execute the query. You might be able to reduce the number of conditions by using the In or NotIn query functions that can be used with numbers, unique identifiers, and strings up to 850 characters.

Next steps

Learn how to page results.

Page results

Megosztás a következőn keresztül:

Filter rows using OData

Comparison operators

Column comparison

Logical operators

Grouping operators

Dataverse query functions

Filter using string values

URL encode special characters

Use wildcard characters

Leading wildcards not supported

Use OData query functions

Manage single quotes

Filter on lookup property

Filter using lookup column property values

Lambda operator examples

Condition limits

Next steps

Visszajelzés

További források

Megosztás a következőn keresztül:

Filter rows using OData

Comparison operators

Column comparison

Logical operators

Grouping operators

Dataverse query functions

Filter using string values

URL encode special characters

Use wildcard characters

Leading wildcards not supported

Use OData query functions

Manage single quotes

Filter based on related data values

Filter on lookup property

Filter using lookup column property values

Filter using values of related collections

Lambda operator examples

Condition limits

Next steps

Visszajelzés

További források