Dataverse Search query
The query operation returns search results based on a search term.
In addition to a search term, the results returned can be influenced by passing values for the following parameters:
| Name | Type | Description | More information |
|---|---|---|---|
search |
string | Required. The text to search with. | search parameter |
count |
bool | Whether to return the total record count. | count parameter |
entities |
string | Limits the scope of search to a subset of tables. | entities parameter |
facets |
string | Facets support the ability to drill down into data results after they've been retrieved. | facets parameter |
filter |
string | Limits the scope of the search results returned. | filter parameter |
options |
string | Options are settings configured to search a search term. | options parameter |
orderby |
string | Specifies how to order the results in order of precedence. | orderby parameter |
skip |
int | Specifies the number of search results to skip. | skip and top parameters |
top |
int | Specifies the number of search results to retrieve. | skip and top parameters |
Parameters
This section includes details about the parameters introduced in the table above.
search parameter
Type: string
Optional: false
The search parameter contains the text to search. It's the only required parameter. Search term must be at least one character long and has a 100 character limit.
Simple Search syntax
By default, the search parameter supports simple search syntax as described in the following table:
| Functionality | Description |
|---|---|
| Boolean operators | AND operator; denoted by +OR operator; denoted by |NOT operator; denoted by - |
| Precedence operators | A search term hotel+(wifi | luxury) searches for results containing the term hotel and either wifi or luxury (or both). |
| Wildcards | Trailing wildcard are supported. For example, Alp* searches for "alpine". |
| Exact matches | A query enclosed in quotation marks " ". |
Note
In order to use any search operators as part of the search text, escape the character by prefixing it with a single backslash (\). Special characters that require escaping include the following: + - & | ! ( ) { } [ ] ^ " ~ * ? : \ /.
For example, an escaped phone number might look like this: \+1\(800\)555\-1234.
Using the options parameter, you can enable Lucerne Query Syntax that enables different operators.
count parameter
Type: bool
Optional: true
Whether to return the total record count. If you don't set this parameter, the Count response property is -1.
entities parameter
Type: string
Optional: true
By default all the tables enabled for search are searched unless you specify a subset using the entities parameter.
When you set an entity, you can also specify which columns you want to return and which columns to search. You can also include filter criteria for the table.
To get a list of tables enabled for the environment, use the Search Status API and look for the tables listed by entitylogicalname within entitystatusresults.
SearchEntity type
Use this type to compose the array of tables to pass to the entities parameter.
| Field Name | Type | Description |
|---|---|---|
name |
string | Required. Logical name of the table. Specifies scope of the query. |
selectColumns |
string[] | Optional. List of columns that needs to be projected when table documents are returned in response. If empty, only the table primary name is returned. |
searchColumns |
string[] | Optional. List of columns to scope the query on. If empty, only the table primary name is searched on. |
filter |
string | Optional. Filters applied on the entity. |
Example
The following is an example of some JSON data that uses the schema described above.
[
{
"name":"account",
"selectColumns":["name","address1_city"],
"searchColumns":["name","address1_city"],
"filter":"modifiedon ge 2018-01-01T00:00:00Z"
},
{
"name":"contact",
"selectColumns":["fullname","address1_city"],
"searchColumns":["fullname","address1_city"],
"filter":"modifiedon ge 2018-01-01T00:00:00Z"
}
]
To use this data, you must escape the string and pass it as the value of the entities parameter in the body of the request:
{
"search": "maria",
"entities":"[{\"name\":\"account\",\"selectColumns\":[\"name\",\"address1_city\"],\"searchColumns\":[\"name\",\"address1_city\"],\"filter\":\"modifiedon ge 2018-01-01T00:00:00Z\"},{\"name\":\"contact\",\"selectColumns\":[\"fullname\",\"address1_city\"],\"searchColumns\":[\"fullname\",\"address1_city\"],\"filter\":\"modifiedon ge 2018-01-01T00:00:00Z\"}]"
}
facets parameter
Type: string
Optional: true
The facet parameter is optional. The string might contain parameters to customize the faceting, expressed as comma-separated name-value pairs. Use facets to group your search results.
Facet definition
Facets are defined as an array of strings, for example:
[
"entityname,count:100",
"account:primarycontactid,count:100",
"ownerid,count:100",
"modifiedon,values:2019-04-27T00:00:00|2020-03-27T00:00:00|2020-04-20T00:00:00|2020-04-27T00:00:00",
"createdon,values:2019-04-27T00:00:00|2020-03-27T00:00:00|2020-04-20T00:00:00|2020-04-27T00:00:00"
]
Each item in the array represents a different way to group the data returned by the query. For each property returned, you can specify appropriate faceting using the values in the following table:
| Facet Type | Description |
|---|---|
count |
The maximum number of facet terms. The default is 10. There's no upper limit |
sort |
Can be set to count, -count, value, -value. Use count to sort descending by count. Use -count to sort ascending by count. Use value to sort ascending by value. Use -value to sort descending by value. |
values |
Set to pipe-delimited numeric or Edm.DateTimeOffset values specifying a dynamic set of facet entry values. The values must be listed in sequential, ascending order to get the expected results. |
interval |
An integer interval greater than zero for numbers, or minute, hour, day, week, month, quarter, year for date time values. |
timeoffset |
Set to ([+-]hh:mm, [+-]hhmm, or [+-]hh). If used, the timeoffset parameter must be combined with the interval option, and only when applied to a field of type Edm.DateTimeOffset. The value specifies the UTC time offset to account for in setting time boundaries. |
Note
count and sort can be combined in the same facet specification, but they cannot be combined with interval or values, and interval and values cannot be combined together.
Set the facets value with an escaped string containing the definition of the facets.
{
"search": "maria",
"facets": "[\"entityname,count:100\",\"account:primarycontactid,count:100\",\"ownerid,count:100\",\"modifiedon,values:2019-04-27T00:00:00|2020-03-27T00:00:00|2020-04-20T00:00:00|2020-04-27T00:00:00\",\"createdon,values:2019-04-27T00:00:00|2020-03-27T00:00:00|2020-04-20T00:00:00|2020-04-27T00:00:00\"]"
}
More information:
- Azure Cognitive Search: Add faceted navigation to a search app
- Azure Cognitive Search REST API > Search Documents > Query Parameters
filter parameter
Type: string
Optional: true
Filters limit the scope of the search results returned. Use filters to exclude unwanted results. This is a top level filter that helps filter common columns across multiple entities like createdon or modifiedon etc.
Apply filters using this syntax: <attribute logical name> <filter> where the table logical name specifies the entity the filter should be applied to.
Filters use the following query operators:
| Operator | Description | Example |
|---|---|---|
| Comparison Operators | ||
eq |
Equal | revenue eq 100000 |
ne |
Not Equal | revenue ne 100000 |
gt |
Greater than | revenue gt 100000 |
ge |
Greater than or equal | revenue ge 100000 |
lt |
Less than | revenue lt 100000 |
le |
Less than or equal | revenue le 100000 |
| Logical Operators | ||
and |
Logical and | revenue lt 100000 and revenue gt 2000 |
or |
Logical or | name eq 'sample' or name eq 'test' |
not |
Logical negation | not name eq 'sample' |
| Grouping Operators | ||
( ) |
Precedence grouping | (name eq 'sample') or name eq 'test') and revenue gt 5000 |
options parameter
Type: string
Optional: true
Options are settings configured to search a search term. Set the options value to a serialized Dictionary<string, string> of these options, such as "{'querytype': 'lucene', 'searchmode': 'all', 'besteffortsearchenabled': 'true', 'grouprankingenabled': 'true'}".
The following table lists the options:
| Option | Description |
|---|---|
querytype |
Values can be simple or lucene Lucerne Query Syntax |
besteffortsearchenabled |
Enables intelligent query workflow to return probable set of results if no good matches are found for the search request terms. |
groupranking |
Enable ranking of results in the response optimized for display in search results pages where results are grouped by table. |
searchmode |
When specified as all the search terms must be matched in order to consider the document as a match. Setting its value to any defaults to matching any word in the search term. |
Lucerne Query Syntax
The Lucene query syntax supports the following functionality:
| Functionality | Description |
|---|---|
| Boolean operators | Provides an expanded set compared to simple query syntax. AND operator; denoted by AND, &&, +OR operator; denoted by OR, ||NOT operator; denoted by NOT, !, – |
| Wildcards | In addition to a trailing wildcard, also supports a leading wildcard. Trailing wildcard – alp*Leading wildcard - /.*pine/ |
| Fuzzy search | Supports queries misspelled by up to two characters.Uniersty~ returns UniversityBlue~1 returns glue, blues |
| Term boosting | Weighs specific terms in a query differently.Rock^2 electronic returns results where the matches to rock are more important than matches to electronic. |
| Proximity search | Returns results where terms are within x words of each other, for more contextual results. For example, "airport hotel"~5 returns results where airport and hotel are within five words of each other, thus boosting the chances of finding a hotel located close to an airport. |
| Regular expression (regex) search | For example, /[mh]otel/ matches motel or hotel. |
orderby parameter
Type: string
Optional: true
Use the orderby parameter to override the default ordering. By default, results are listed in descending order of relevance score (@search.score). For results with identical scores, the ordering is random. You can only use this parameter when query type is lucene with wildcard characters in the query string.
Use a list of comma-separated clauses where each clause consists of a column name followed by asc (ascending, which is the default) or desc (descending).
For a set of results that contain multiple table types, the list of clauses for orderby must be globally applicable (for example, modifiedon, createdon, @search.score). For example, to get results ranked (in order of precedence) by relevance, followed by the most recently modified records listed higher:
"orderby": ["@search.score desc", "modifiedon desc"]
If the query request includes a filter for a specific table type, orderby can optionally specify table-specific columns.
skip and top parameters
Type: int
Optional: true
You can use these parameters together with the count parameter to create a paged experience.
By default, up to 50 results are returned at a time. You can use top to raise it as high as 100, but more commonly you'll use top to specify a smaller result set, such as 10, and then use skip to bypass previously returned results when the user moves to the next page.
Response
The response from the query operation is an escaped string that includes JSON data.
The unescaped response contains JSON using the following properties.
| Name | Type | Description |
|---|---|---|
Error |
ErrorDetail | Provides error information from Azure Cognitive search. |
Value |
QueryResult[] |
A collection of matching records. |
Facets |
Dictionary<string, FacetResult[]> |
If facets were requested in the query, a dictionary of facet values. |
QueryContext |
QueryContext | This property is used for backend search. It's included for future feature releases and isn't currently used. |
Count |
long | If "Count": true is included in the body of the request, the count of all documents that match the search, ignoring top and skip |
Response Types
This section describes the types returned with the response.
ErrorDetail
The Azure Cognitive search error returned as part of the response.
| Name | Type | Description |
|---|---|---|
code |
string | The error code. |
message |
string | The error message. |
propertybag |
Dictionary<string, object> |
More error information. |
QueryResult
Each QueryResult item returned in the response Value property represents a record in Dataverse.
| Name | Type | Description |
|---|---|---|
Id |
string | The identifier of the record. |
EntityName |
string | The logical name of the table. |
ObjectTypeCode |
int | The object type code. |
Attributes |
Dictionary<string, object> |
Record attributes |
Highlights |
Dictionary<string, string[]> |
The highlights. |
Score |
double | The document score. |
FacetResult
A facet query result that reports the number of documents with a field falling within a particular range or having a particular value or interval.
| Name | Type | Description |
|---|---|---|
count |
long? | The count of documents falling within the bucket described by this facet. |
from |
object | Value indicating the inclusive lower bound of the facet's range, or null to indicate that there's no lower bound. |
to |
object | Value indicating the exclusive upper bound of the facet's range, or null to indicate that there's no upper bound. |
type |
Value | Range |
Type of the facet. |
value |
object | Value of the facet, or the inclusive lower bound if it's an interval facet. |
optionalvalue |
object | Another or optional value of the facet, populated while faceting on lookups. |
QueryContext
The query context returned as part of response. This property is used for backend search. It's included for future feature releases and isn't currently used.
| Name | Type | Description |
|---|---|---|
originalquery |
string | The query string as specified in the request. |
alteredquery |
string | The query string that Dataverse search used to perform the query. Dataverse search uses the altered query string if the original query string contained spelling mistakes or didn't yield optimal results. |
reason |
string[] | The reasons behind query alter decision by Dataverse search. |
spellsuggestions |
string[] | The spell suggestion that is the likely words that represent user's intent. Populated only when Dataverse alters the query search due to spell check. |
Examples
The following examples show how to use the query operation. These examples perform a search operation on the account and contact tables name and fullname columns respectively, for records created later than August 15, 2022 and orders the top seven results by the createdon field, descending.
This example is from the SDK for .NET search operations sample on GitHub. The static OutputSearchQuery method accepts a value for the search parameter.
/// <summary>
/// Demonstrate query API
/// </summary>
/// <param name="service">The authenticated IOrganizationService instance to use.</param>
/// <param name="searchTerm">The term to search for</param>
/// <returns></returns>
static void OutputSearchQuery(IOrganizationService service, string searchTerm)
{
Console.WriteLine("OutputSearchQuery START\n");
searchqueryRequest request = new() {
search = searchTerm,
count = true,
top = 7,
entities = JsonConvert.SerializeObject(new List<SearchEntity>()
{
new SearchEntity()
{
Name = "account",
SelectColumns = new List<string>() { "name", "createdon" },
SearchColumns = new List<string>() { "name" },
Filter = "statecode eq 0"
},
new SearchEntity()
{
Name = "contact",
SelectColumns = new List<string>() { "fullname", "createdon" },
SearchColumns = new List<string>() { "fullname" },
Filter = "statecode eq 0"
}
}),
orderby = JsonConvert.SerializeObject(new List<string>() { "createdon desc" }),
filter = "createdon gt 2022-08-15"
};
var searchqueryResponse = (searchqueryResponse)service.Execute(request);
var queryResults = JsonConvert.DeserializeObject<SearchQueryResults>(searchqueryResponse.response);
Console.WriteLine($"\tCount:{queryResults.Count}");
Console.WriteLine("\tValue:");
queryResults.Value.ForEach(result =>
{
Console.WriteLine($"\t\tId:{result.Id}");
Console.WriteLine($"\t\tEntityName:{result.EntityName}");
Console.WriteLine($"\t\tObjectTypeCode:{result.ObjectTypeCode}");
Console.WriteLine("\t\tAttributes:");
foreach (string key in result.Attributes.Keys)
{
Console.WriteLine($"\t\t\t{key}:{result.Attributes[key]}");
}
Console.WriteLine("\t\tHighlights:");
foreach (string key in result.Highlights.Keys)
{
Console.WriteLine($"\t\t\t{key}:");
foreach (string value in result.Highlights[key])
{
Console.WriteLine($"\t\t\t\t{value}:");
}
}
Console.WriteLine($"\t\tScore:{result.Score}\n");
});
Console.WriteLine("OutputSearchQuery END\n");
}
Output
When you invoke the OutputSearchQuery method with an authenticated instance of the ServiceClient class with the searchTerm set to "Contoso":
OutputSearchQuery(service: serviceClient, searchTerm: "Contoso");
The output looks something like the following:
OutputSearchQuery START
Count:1
Value:
Id:8b35eda1-ef69-ee11-9ae7-000d3a88a4a2
EntityName:account
ObjectTypeCode:0
Attributes:
@search.objecttypecode:1
name:Contoso Pharmaceuticals (sample)
createdon:10/13/2023 5:41:21 PM
createdon@OData.Community.Display.V1.FormattedValue:10/13/2023 5:41 PM
Highlights:
name:
{crmhit}Contoso{/crmhit} Pharmaceuticals (sample):
Score:4.986711
OutputSearchQuery END
Supporting classes
The OutputSearchQuery method depends on the following supporting classes to send the request and process the result:
searchqueryRequest and searchqueryResponse classes
These classes are generated using Power Platform CLI pac modelbuilder build command as described in Generate early-bound classes for the SDK for .NET.
SearchEntity class
Used to compose SearchEntity type data.
public sealed class SearchEntity
{
/// <summary>
/// Gets or sets the logical name of the table. Specifies scope of the query.
/// </summary>
[DataMember(Name = "name", IsRequired = true)]
public string Name { get; set; }
/// <summary>
/// Gets or sets the list of columns that needs to be projected when table documents are returned in response.
/// If empty, only PrimaryName will be returned.
/// </summary>
[DataMember(Name = "selectcolumns")]
public List<string> SelectColumns { get; set; }
/// <summary>
/// Gets or sets the list of columns to scope the query on.
/// If empty, only PrimaryName will be searched on.
/// </summary>
[DataMember(Name = "searchcolumns")]
public List<string> SearchColumns { get; set; }
/// <summary>
/// Gets or sets the filters applied on the entity.
/// </summary>
[DataMember(Name = "filter")]
public string Filter { get; set; }
}
SearchQueryResults class
Use to deserialize JSON data from the searchqueryResponse.response string property.
public sealed class SearchQueryResults
{
/// <summary>
/// Provides error information from Azure Cognitive search.
/// </summary>
public ErrorDetail? Error { get; set; }
/// <summary>
/// A collection of matching records.
/// </summary>
public List<QueryResult>? Value { get; set; }
/// <summary>
/// If facets were requested in the query, a dictionary of facet values.
/// </summary>
public Dictionary<string, IList<FacetResult>>? Facets { get; set; }
/// <summary>
/// The query context returned as part of response. This property is used for backend search. It is included for future feature releases and is not currently used.
/// </summary>
public QueryContext? QueryContext { get; set; }
/// <summary>
/// If `"Count": true` is included in the body of the request, the count of all documents that match the search, ignoring top and skip.
/// </summary>
public long Count { get; set; }
}
ErrorDetail class
Used to deserialize the ErrorDetail data.
public sealed class ErrorDetail
{
/// <summary>
/// Gets or sets the error code.
/// </summary>
[DataMember(Name = "code")]
public string Code { get; set; }
/// <summary>
/// Gets or sets the error message.
/// </summary>
[DataMember(Name = "message")]
public string Message { get; set; }
/// <summary>
/// Gets or sets additional error information.
/// </summary>
[DataMember(Name = "propertybag")]
public Dictionary<string, object> PropertyBag { get; set; }
}
QueryResult class
Used to deserialize the QueryResult data.
public sealed class QueryResult
{
/// <summary>
/// Gets or sets the identifier of the record
/// </summary>
public string Id { get; set; }
/// <summary>
/// Gets or sets the logical name of the table
/// </summary>
public string EntityName { get; set; }
/// <summary>
/// Gets or sets the object type code
/// </summary>
public int ObjectTypeCode { get; set; }
/// <summary>
/// Gets or sets the record attributes
/// </summary>
public Dictionary<string, object> Attributes { get; set; }
/// <summary>
/// Gets or sets the highlights
/// </summary>
public Dictionary<string, string[]> Highlights { get; set; }
// Gets or sets the document score
public double Score { get; set; }
}
FacetResult class
Used to deserialize the FacetResult data.
public sealed class FacetResult
{
/// <summary>
/// Gets or sets the count of documents falling within the bucket described by this facet.
/// </summary>
[DataMember(Name = "count")]
public long? Count { get; set; }
/// <summary>
/// Gets or sets value indicating the inclusive lower bound of the facet's range, or null to indicate that there is no lower bound.
/// </summary>
[DataMember(Name = "from")]
public object From { get; set; }
/// <summary>
/// Gets or sets value indicating the exclusive upper bound of the facet's range, or null to indicate that there is no upper bound.
/// </summary>
[DataMember(Name = "to")]
public object To { get; set; }
/// <summary>
/// Gets or sets type of the facet - Value or Range.
/// </summary>
[DataMember(Name = "type")]
public FacetType Type { get; set; }
/// <summary>
/// Gets or sets value of the facet, or the inclusive lower bound if it's an interval facet.
/// </summary>
[DataMember(Name = "value")]
public object Value { get; set; }
/// <summary>
/// Gets or sets additional/ Optional value of the facet, will be populated while faceting on lookups.
/// </summary>
[DataMember(Name = "optionalvalue")]
public object OptionalValue { get; set; }
}
FacetType class
Specifies the type of a facet query result.
public enum FacetType
{
/// <summary>
/// The facet counts documents with a particular field value.
/// </summary>
[EnumMember(Value = "value")]
Value = 0,
/// <summary>
/// The facet counts documents with a field value in a particular range.
/// </summary>
[EnumMember(Value = "range")]
Range = 1,
}
QueryContext class
Used to deserialize the QueryContext data.
public sealed class QueryContext
{
/// <summary>
/// Gets or sets the query string as specified in the request.
/// </summary>
[DataMember(Name = "originalquery")]
public string OriginalQuery { get; set; }
/// <summary>
/// Gets or sets the query string that Dataverse search used to perform the query.
/// Dataverse search uses the altered query string if the original query string contained spelling mistakes or did not yield optimal results.
/// </summary>
[DataMember(Name = "alteredquery")]
public string AlteredQuery { get; set; }
/// <summary>
/// Gets or sets the reason behind query alter decision by Dataverse search.
/// </summary>
[DataMember(Name = "reason")]
public List<string> Reason { get; set; }
/// <summary>
/// Gets or sets the spell suggestion that are the likely words that represent user's intent.
/// This will be populated only when the query was altered by Dataverse search due to spell check.
/// </summary>
[DataMember(Name = "spellsuggestions")]
public List<string> SpellSuggestions { get; set; }
}
See also
Search for Dataverse records
Dataverse Search suggest
Dataverse Search autocomplete
Dataverse Search statistics and status
Dataverse legacy search