Filter rows using OData
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.
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
, andendswith
. 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''.
Filter based on related data values
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"
}
}
}
]
}
Filter using values of related collections
Use the Lambda operators any
and all
to evaluate values in a collection to filter the results.
any
: Returnstrue
if the expression applied is true for any member of the collection; otherwise, it returns false.- The
any
operator without an argument returnstrue
if the collection isn't empty.
- The
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.
Palaute
https://aka.ms/ContentUserFeedback.
Tulossa pian: Vuoden 2024 aikana poistamme asteittain GitHub Issuesin käytöstä sisällön palautemekanismina ja korvaamme sen uudella palautejärjestelmällä. Lisätietoja on täällä:Lähetä ja näytä palaute kohteelle