--- title: Define and call data queries description: You can define and call queries using the REST or Resource API. Queries are supported on both managed and system objects, and you can define them through: component: pingoneaic page_id: pingoneaic:idm-objects:queries canonical_url: https://docs.pingidentity.com/pingoneaic/idm-objects/queries.html keywords: ["Data Object Model", "REST API", "Virtual Properties"] section_ids: query-filters: Common filter expressions (_queryFilter) special-char-queries: Special characters in queries constructing-queries: Construct queries query-comp-expression: Comparison expressions query-presence: Presence expressions query-literal: Literal expressions query-in: In expression clause filter-expand-relation: Filter expanded relationships query-complex: Complex expressions filter_objects_in_arrays: Filter objects in arrays paging-query-results: Page query results sorting-query-results: Sort query results execute-on-retrieve: Recalculate properties based on other properties (virtual) in queries --- # Define and call data queries You can define and call queries using the REST or Resource API. Queries are supported on both managed and system objects, and you can define them through: * Common filter expressions using the `_queryFilter` keyword. * Parameterized, or predefined queries, such as the keyword `_pageSize`. ## Common filter expressions (`_queryFilter`) The Advanced Identity Cloud REST API defines common filter expressions that enable you to form arbitrary queries using a number of supported filter operations. This query capability is the standard way to query data if no predefined query exists and is supported for all managed and system objects. Common filter expressions are useful because they do not require knowledge of how the object is stored, and they do not require additions to the repository configuration. Common filter expressions are called with the `_queryFilter` keyword. The following example uses a common filter expression to retrieve managed user objects whose user name is Smith: ``` curl \ --header "Authorization: Bearer " \ --header "Accept-API-Version: resource=1.0" \ 'https:///openidm/managed/realm-name_user?_queryFilter=userName+eq+"smith"' ``` The filter is URL encoded in this example. The corresponding filter using the resource API would be: ```javascript openidm.query("managed/realm-name_user", { "_queryFilter" : '/userName eq "smith"' }); ``` ### Special characters in queries JavaScript query invocations are not subject to the same URL-encoding requirements as GET requests. Since JavaScript supports the use of single quotes, it is not necessary to escape the double quotes from most examples in this guide. Make sure to protect against pulling in data that could contain special characters, such as double-quotes (`"`). The following example shows one method of handling special characters: ```json "correlationQuery" : { "type" : "text/javascript", "source" : "var qry = {'_queryFilter': org.forgerock.util.query.QueryFilter.equalTo('uid', source.userName).toString()}; qry" } ``` ### Construct queries The `openidm.query` function lets you query managed and system objects. The query syntax is `openidm.query(id, params)`, where `id` specifies the object upon the query should be performed, and `params` provides the parameters passed to the query (the `_queryFilter`). For example: ```javascript var equalTo = org.forgerock.util.query.QueryFilter.equalTo; queryParams = { "_queryFilter": equalTo("uid", value).toString() }; openidm.query("managed/realm-name_user", queryParams) ``` Over the REST interface, the query filter is specified as `_queryFilter=filter`, for example: ``` curl \ --header "Authorization: Bearer " \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'https:///openidm/managed/realm-name_user?_queryFilter=userName+eq+"Smith"' ``` | | | | - | ----------------------------------------------------------------------------------------------------------------------------- | | | In `_queryFilter` expressions, string values *must* use double-quotes. Numeric and boolean expressions should not use quotes. | When called over REST, you must URL encode the filter expression. The following examples show the filter expressions using the Resource API and the REST API but do not show the URL encoding to make them easier to read. The filter expression is constructed from the building blocks shown in this section. In these expressions, the simplest json-pointer is a field of the JSON resource, such as `userName` or `id`. A JSON pointer can, however, point to nested elements. | | | | - | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | You can also use the negation operator (**`!`**) in query construction. For example, a `_queryFilter=!(userName+eq+"jdoe")` query would return every `userName` except for `jdoe`. | #### Comparison expressions You can use comparison query filters for objects and object array properties that: > **Collapse: Equal a specified value** > > This is the associated JSON comparison expression: `json-pointer eq json-value`. > > Example 1 > > ```json > "_queryFilter" : '/givenName eq "Dan"' > ``` > > The following REST call returns the user name and given name of all managed users whose first name (`givenName`) is "Dan": > > ``` > curl \ > --header "Authorization: Bearer " \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'https:///openidm/managed/realm-name_user?_queryFilter=givenName+eq+"Dan"&_fields=userName,givenName' > { > "result": [ > { > "givenName": "Dan", > "userName": "dlangdon" > }, > { > "givenName": "Dan", > "userName": "dcope" > }, > { > "givenName": "Dan", > "userName": "dlanoway" > } > ], > ... > } > ``` > > Example 2 > > ```json > "_queryFilter" : "/stringArrayField eq 'foo'" > ``` > > The following REST call returns role entries where a value within the `stringArrayField` array equals "foo": > > ``` > curl \ > --header "Authorization: Bearer " \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'https:///openidm/managed/realm-name_role?_queryFilter=stringArrayField+eq+"foo"' > { > "result": [ > { > "_id": "admin2", > "_rev": "0", > "name": "admin2", > "stringArrayField": [ > "foo", > "bar" > ] > } > ], > ... > } > ``` > > [Additional information about PostgreSQL JSON functions](https://www.postgresql.org/docs/9.5/functions-json.html). > **Collapse: Contain a specified value** > > This is the associated JSON comparison expression: `json-pointer co json-value`. > > Example > > ```json > "_queryFilter" : '/givenName co "Da"' > ``` > > The following REST call returns the username and given name of all managed users whose first name (`givenName`) contains "Da": > > ``` > curl \ > --header "Authorization: Bearer " \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'https:///openidm/managed/realm-name_user?_queryFilter=givenName+co+"Da"&_fields=userName,givenName' > { > "result": [ > { > "givenName": "Dave", > "userName": "djensen" > }, > { > "givenName": "David", > "userName": "dakers" > }, > { > "givenName": "Dan", > "userName": "dlangdon" > }, > { > "givenName": "Dan", > "userName": "dcope" > }, > { > "givenName": "Dan", > "userName": "dlanoway" > }, > { > "givenName": "Daniel", > "userName": "dsmith" > }, > ... > ], > "resultCount": 10, > "pagedResultsCookie": null, > "remainingPagedResults": -1 > } > ``` > **Collapse: Start with a specified value** > > This is the associated JSON comparison expression: `json-pointer sw json-value`. > > Example > > ```json > "_queryFilter" : '/sn sw "Jen"' > ``` > > The following REST call returns the user names of all managed users whose last name (`sn`) starts with "Jen": > > ``` > curl \ > --header "Authorization: Bearer " \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'https:///openidm/managed/realm-name_user?_queryFilter=sn+sw+"Jen"&_fields=userName' > { > "result": [ > { > "userName": "bjensen" > }, > { > "userName": "djensen" > }, > { > "userName": "cjenkins" > }, > { > "userName": "mjennings" > } > ], > "resultCount": 4, > "pagedResultsCookie": null, > "remainingPagedResults": -1 > } > ``` > **Collapse: Are less than a specified value** > > This is the associated JSON comparison expression: `json-pointer lt json-value`. > > Example > > ```json > "_queryFilter" : '/employeeNumber lt 5000' > ``` > > The following REST call returns the user names of all managed users whose `employeeNumber` is lower than 5000: > > ``` > curl \ > --header "Authorization: Bearer " \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'https:///openidm/managed/realm-name_user?_queryFilter=employeeNumber+lt+5000&_fields=userName,employeeNumber' > { > "result": [ > { > "employeeNumber": 4907, > "userName": "jnorris" > }, > { > "employeeNumber": 4905, > "userName": "afrancis" > }, > { > "employeeNumber": 3095, > "userName": "twhite" > }, > { > "employeeNumber": 3921, > "userName": "abasson" > }, > { > "employeeNumber": 2892, > "userName": "dcarter" > }, > ... > ], > "resultCount": 4999, > "pagedResultsCookie": null, > "remainingPagedResults": -1 > } > ``` > **Collapse: Are less than or equal to a specified value** > > This is the associated JSON comparison expression: `json-pointer le json-value`. > > Example > > ```json > "_queryFilter" : '/employeeNumber le 5000' > ``` > > The following REST call returns the user names of all managed users whose `employeeNumber` is 5000 or less: > > ``` > curl \ > --header "Authorization: Bearer " \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'https:///openidm/managed/realm-name_user?_queryFilter=employeeNumber+le+5000&_fields=userName,employeeNumber' > { > "result": [ > { > "employeeNumber": 4907, > "userName": "jnorris" > }, > { > "employeeNumber": 4905, > "userName": "afrancis" > }, > { > "employeeNumber": 3095, > "userName": "twhite" > }, > { > "employeeNumber": 3921, > "userName": "abasson" > }, > { > "employeeNumber": 2892, > "userName": "dcarter" > }, > ... > ], > "resultCount": 5000, > "pagedResultsCookie": null, > "remainingPagedResults": -1 > } > ``` > **Collapse: Are greater than a specified value** > > This is the associated JSON comparison expression: `json-pointer gt json-value` > > Example > > ```json > "_queryFilter" : '/employeeNumber gt 5000' > ``` > > The following REST call returns the user names of all managed users whose `employeeNumber` is higher than 5000: > > ``` > curl \ > --header "Authorization: Bearer " \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'https:///openidm/managed/realm-name_user?_queryFilter=employeeNumber+gt+5000&_fields=userName,employeeNumber' > { > "result": [ > { > "employeeNumber": 5003, > "userName": "agilder" > }, > { > "employeeNumber": 5011, > "userName": "bsmith" > }, > { > "employeeNumber": 5034, > "userName": "bjensen" > }, > { > "employeeNumber": 5027, > "userName": "cclarke" > }, > { > "employeeNumber": 5033, > "userName": "scarter" > }, > ... > ], > "resultCount": 1458, > "pagedResultsCookie": null, > "remainingPagedResults": -1 > } > ``` > **Collapse: Are greater than or equal to a specified value** > > This is the associated JSON comparison expression: `json-pointer ge json-value`. > > Example > > ```json > "_queryFilter" : '/employeeNumber ge 5000' > ``` > > The following REST call returns the user names of all managed users whose `employeeNumber` is 5000 or greater: > > ``` > curl \ > --header "Authorization: Bearer " \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'https:///openidm/managed/realm-name_user?_queryFilter=employeeNumber+ge+5000&_fields=userName,employeeNumber' > { > "result": [ > { > "employeeNumber": 5000, > "userName": "agilder" > }, > { > "employeeNumber": 5011, > "userName": "bsmith" > }, > { > "employeeNumber": 5034, > "userName": "bjensen" > }, > { > "employeeNumber": 5027, > "userName": "cclarke" > }, > { > "employeeNumber": 5033, > "userName": "scarter" > }, > ... > ], > "resultCount": 1457, > "pagedResultsCookie": null, > "remainingPagedResults": -1 > } > ``` | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | | Although specific system endpoints also support `EndsWith` and `ContainsAllValues` queries, such queries are *not supported* for managed objects and have not been tested with all supported ICF connectors. | #### Presence expressions The following examples show how you can build filters using a presence expression, shown as `pr`. The presence expression is a filter that returns all records with a given attribute. A presence expression filter evaluates to `true` when a `json-pointer pr` matches any object in which the json-pointer is present and contains a non-null value. Consider the following expression: ```json "_queryFilter" : '/mail pr' ``` The following REST call uses that expression to return the mail addresses for all managed users with a `mail` property: ``` curl \ --header "Authorization: Bearer " \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'https:///openidm/managed/realm-name_user?_queryFilter=mail+pr&_fields=mail' { "result": [ { "mail": "jdoe@exampleAD.com" }, { "mail": "bjensen@example.com" } ], "resultCount": 2, "pagedResultsCookie": null, "remainingPagedResults": -1 } ``` Depending on the connector, you can apply the presence filter on system objects. The following query returns the email address of all users in a CSV file who have the `email` attribute in their entries: ``` curl \ --header "Authorization: Bearer " \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'https:///openidm/system/csvfile/account?_queryFilter=email+pr&_fields=email' { "result": [ { "_id": "bjensen", "email": "bjensen@example.com" }, { "_id": "scarter", "email": "scarter@example.com" } ], "resultCount": 2, "pagedResultsCookie": "MA%3D%3D", "totalPagedResultsPolicy": "NONE", "totalPagedResults": -1, "remainingPagedResults": -1 } ``` | | | | - | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | Not all connectors support the presence filter. In most cases, you can replicate the behavior of the presence filter with an "equals" (`eq`) query, such as `_queryFilter=email+eq+"*"` | #### Literal expressions A literal expression is a boolean: * `true` matches any object in the resource. * `false` matches no object in the resource. For example, you can list the `_id` of all managed objects as follows: ``` curl \ --header "Authorization: Bearer " \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'https:///openidm/managed/realm-name_user?_queryFilter=true&_fields=_id' { "result": [ { "_id": "d2e29d5f-0d74-4d04-bcfe-b1daf508ad7c" }, { "_id": "709fed03-897b-4ff0-8a59-6faaa34e3af6" } ], "resultCount": 2, "pagedResultsCookie": null, "remainingPagedResults": -1 } ``` #### `In` expression clause IDM provides limited support for the [in expression clause](#query-in). You can use this clause for queries on singleton string properties or arrays. The `in` query expression is not supported through the IDM admin console or for use by [delegated administrators](../idm-auth/delegated-admin.html#using-delegated-admin). The `in` operator is shorthand for multiple `OR` conditions. | | | | - | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | The following example command includes escaped characters. For readability, the non-escaped URL syntax is:```none https:///openidm/managed/realm-name_user?_pageSize=1000&_fields=userName&_queryFilter=/userName in '["user4a","user3a"]' ``` | ``` curl \ --header "Authorization: Bearer " \ --header "Accept-API-Version: resource=1.0" \ --request GET \ "https:///openidm/managed/realm-name_user?_pageSize=1000&_fields=userName&_queryFilter=userName%20in%20'%5B%22user4a%22%2C%22user3a%22%5D'" { "result": [ { "_id": "e32f9a3d-0039-4cb0-82d7-347cb808672e", "_rev": "000000000ae18357", "userName": "user3a" }, { "_id": "120625c5-cfe7-48e7-b66a-6a0a0f9d2901", "_rev": "000000005ad98467", "userName": "user4a" } ], "resultCount": 2, "pagedResultsCookie": null, "totalPagedResultsPolicy": "NONE", "totalPagedResults": -1, "remainingPagedResults": -1 } ``` #### Filter expanded relationships You can use `_queryFilter` to filter expanded relationships from a collection, such as `authzRoles`. The following example queries the `manager-int` authorization role of a user: ``` curl \ --header "Authorization: Bearer " \ --header "Accept-API-Version: resource=1.0" \ --request GET \ "https:///openidm/managed/realm-name_user/b70293db-8743-45a7-9215-1ca8fd8a0073/authzRoles?_queryFilter=name+eq+'manager-int'&_fields=*" { "result": [ { "_id": "b1d78144-7029-4135-8e73-85efe0a40b6b", "_rev": "00000000d4b8ab97", "_ref": "internal/role/c0a38233-c0f2-477d-8f18-f5485b7d002f", "_refResourceCollection": "internal/role", "_refResourceId": "c0a38233-c0f2-477d-8f18-f5485b7d002f", "_refProperties": { "_grantType": "", "_id": "b1d78144-7029-4135-8e73-85efe0a40b6b", "_rev": "00000000d4b8ab97" }, "name": "manager-int", "description": "manager-int-desc", "temporalConstraints": null, "condition": null, "privileges": null } ], "resultCount": 1, "pagedResultsCookie": null, "totalPagedResultsPolicy": "NONE", "totalPagedResults": -1, "remainingPagedResults": -1 } ``` #### Complex expressions You can combine expressions using the boolean operators `and`, `or`, and `!` (not). The following example queries managed user objects located in London with the last name Jensen: ``` curl \ --header "Authorization: Bearer " \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'https:///openidm/managed/realm-name_user/?_queryFilter=city+eq+"London"and+sn+eq"Jensen"&_fields=userName,givenName,sn' { "result": [ { "sn": "Jensen", "givenName": "Clive", "userName": "cjensen" }, { "sn": "Jensen", "givenName": "Dave", "userName": "djensen" }, { "sn": "Jensen", "givenName": "Margaret", "userName": "mjensen" } ], "resultCount": 3, "pagedResultsCookie": null, "remainingPagedResults": -1 } ``` #### Filter objects in arrays Use query grouping to perform your query on properties within an array. For example, to query `effectiveRoles` for users who have the `testManagedRole`, check the `_refResourceId` inside the `effectiveRoles` array: ``` curl \ --header "Authorization: Bearer " \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'https:///openidm/managed/realm-name_user/?_queryFilter=/effectiveRoles\[/_refResourceId+eq+"testManagedRole"]&_fields=userName,givenName,sn,effectiveRoles' { "result": [ { "_id": "917bc052-ef39-4add-ae05-0a278e2de9c0", "_rev": "200bc5d6-7cc1-4648-a854-3137f3d9c103-1565", "userName": "scarter", "sn": "Carter", "givenName": "Steven", "effectiveRoles": [ { "_refResourceCollection": "managed/realm-name_role", "_refResourceId": "testManagedRole", "_ref": "managed/realm-name_role/testManagedRole" } ] }, { "_id": "aca0042c-9f4c-4ad5-8cf7-aca0adeb3470", "_rev": "200bc5d6-7cc1-4648-a854-3137f3d9c103-1545", "userName": "jdoe", "sn": "Doe", "givenName": "John", "effectiveRoles": [ { "_refResourceCollection": "managed/realm-name_role", "_refResourceId": "testManagedRole", "_ref": "managed/realm-name_role/testManagedRole" } ] } ], "resultCount": 2, "pagedResultsCookie": null, "totalPagedResultsPolicy": "NONE", "totalPagedResults": -1, "remainingPagedResults": -1 } ``` | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | Since `curl` uses brackets (`[]`, `{}`) for processing, you need to escape your brackets with a `\`. This may be unnecessary when you use a different method to call IDM. | ### Page query results The common filter query mechanism supports paged query results for managed objects, and for some system objects, depending on the system resource. There are two ways to page objects in a query: * Using a cookie based on the value of a specified sort key. * Using an offset that specifies how many records should be skipped before the first result is returned. These methods are implemented with the following query parameters: * `_pagedResultsCookie` Opaque cookie used by the server to keep track of the position in the search results. The format of the cookie is a base-64 encoded version of the value of the unique sort key property. The value of the returned cookie is URL-encoded to prevent values such as `+` from being incorrectly translated. You cannot page results without sorting them (using the `_sortKeys` parameter). If you do not specify a sort key, the `_id` of the record is used as the default sort key. At least one of the specified sort key properties must be a unique value property, such as `_id`. | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | | For paged searches on generic mappings, you should sort on the `_id` property, because this is the only property that is stored outside of the JSON blob. If you sort on something other than `_id`, the search will incur a performance hit because IDM has to pull the entire result set and then sort it. | The server provides the cookie value on the first request. You should then supply the cookie value in subsequent requests until the server returns a null cookie, meaning that the final page of results has been returned. The `_pagedResultsCookie` parameter is supported only for filtered queries, that is, when used with the `_queryFilter` parameter. You cannot use the `_pagedResultsCookie` with a `_queryId`. The `_pagedResultsCookie` and `_pagedResultsOffset` parameters are mutually exclusive and cannot be used together. Paged results are enabled only if the `_pageSize` is a non-zero integer. * `_pagedResultsOffset` Specifies the index within the result set of the number of records to be skipped before the first result is returned. The format of the `_pagedResultsOffset` is an integer value. When the value of `_pagedResultsOffset` is greater than or equal to 1, the server returns pages, starting after the specified index. This request assumes that the `_pageSize` is set, and not equal to zero. For example, if the result set includes 10 records, the `_pageSize` is 2, and the `_pagedResultsOffset` is 6, the server skips the first 6 records, then returns 2 records, 7 and 8. The `_remainingPagedResults` value would be 2, the last two records (9 and 10) that have not yet been returned. If the offset points to a page beyond the last of the search results, the result set returned is empty. * `_pageSize` An optional parameter indicating that query results should be returned in pages of the specified size. For all paged result requests other than the initial request, a cookie should be provided with the query request. The default behavior is not to return paged query results. If set, this parameter should be an integer value, greater than zero. When a `_pageSize` is specified, and non-zero, the server calculates the `totalPagedResults` in accordance with the `totalPagedResultsPolicy` and provides the value as part of the response. If a count policy is specified (`_totalPagedResultsPolicy=EXACT`), the `totalPagedResults` returns the total result count. If no count policy is specified in the query, or if `_totalPagedResultsPolicy=NONE`, result counting is disabled, and the server returns a value of -1 for `totalPagedResults`. The following example shows a query that requests two results with a `totalPagedResultsPolicy` of `EXACT`: ``` curl \ --header "Authorization: Bearer " \ --header "Accept-API-Version: resource=1.0" \ --request GET \ "https:///openidm/managed/realm-name_user?_queryFilter=true&_pageSize=2&_totalPagedResultsPolicy=EXACT" { "result": [ { "_id": "adonnelly", "_rev": "0", "userName": "adonnelly", "givenName": "Abigail", "sn": "Donnelly", "telephoneNumber": "12345678", "active": "true", "mail": "adonnelly@example.com", "accountStatus": "active", "effectiveRoles": [], "effectiveAssignments": [] }, { "_id": "bjensen", "_rev": "0", "userName": "bjensen", "givenName": "Babs", "sn": "Jensen", "telephoneNumber": "12345678", "active": "true", "mail": "bjensen@example.com", "accountStatus": "active", "effectiveRoles": [], "effectiveAssignments": [] } ], "resultCount": 2, "pagedResultsCookie": "eyIvX2lkIjoiYm11cnJheSJ9", "totalPagedResultsPolicy": "EXACT", "totalPagedResults": 22, "remainingPagedResults": -1 } ``` The `totalPagedResults` and `_remainingPagedResults` parameters are not supported for all queries. Where they are not supported, their returned value is always `-1`. In addition, counting query results using these parameters is not currently supported for a DS repository. Requesting the total result count (with `_totalPagedResultsPolicy=EXACT`) incurs a performance cost on the query. Queries that return large data sets will have a significant impact on heap requirements, particularly if they are run in parallel with other large data requests. To avoid out of memory errors, analyze your data requirements, set the heap configuration appropriately, and modify access controls to restrict requests on large data sets. ### Sort query results For common filter query expressions, you can sort the results of a query using the `_sortKeys` parameter. This parameter takes a comma-separated list as a value and orders the way the JSON result is returned based on this list. The `_sortKeys` parameter is not supported for predefined queries. | | | | - | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | * Pagination using `_pageSize` is recommended if you intend to use `_sortKeys`. If you do not paginate your query, the data you are querying must be indexed. * When viewing data that is persisted in Advanced Identity Cloud's data store and sorted by un-indexed `_sortKeys`, the `_pageSize` parameter must be less than or equal to the `index-entry-limit` as configured in DS (default value is 4000). | The following query returns all users with the `givenName` Dan and sorts the results alphabetically, according to surname (`sn`): ``` curl \ --header "Authorization: Bearer " \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'https:///openidm/system/ldap/account?_queryFilter=givenName+eq+"Dan"&_fields=givenName,sn&_sortKeys=sn' { "result": [ { "sn": "Cope", "givenName": "Dan" }, { "sn": "Langdon", "givenName": "Dan" }, { "sn": "Lanoway", "givenName": "Dan" } ], "resultCount": 3, "pagedResultsCookie": null, "remainingPagedResults": -1 } ``` | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | | When you query a relationship field, fields that belong to the related object are not available as `_sortKeys`. For example, if you query a list of a manager's reports, you cannot sort by the reports' last names. This is because the available `_sortKeys` are based on the object being queried, which in the case of [relationships](relationships.html) is a list of references to other objects, not the objects themselves. | ### Recalculate properties based on other properties (virtual) in queries For managed objects, IDM includes an [*onRetrieve*](../idm-scripting/script-triggers-managedConfig.html#managed_object_configuration_object) script hook that enables you to recalculate property values, known as [virtual properties](managed-object-virtual-properties.html), when an object is retrieved as the result of a query. To use the `onRetrieve` trigger, the query must include the `executeOnRetrieve` parameter, for example: ``` curl \ --header "Authorization: Bearer " \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'https:///openidm/managed/realm-name_user?_queryFilter=sn+eq+"Jensen"&executeOnRetrieve=true' ``` If a query includes `executeOnRetrieve`, the query recalculates virtual property values, based on the current state of the system. The result of the query will be the same as a `read` on a specific object, because reads always recalculate virtual property values. If a query does not include `executeOnRetrieve`, the query returns the virtual properties of an object based on the value that is persisted in the repository. Virtual property values are not recalculated. For performance reasons, `executeOnRetrieve` is `false` by default. | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | Virtual properties that use [*queryConfig*](managed-object-virtual-properties.html#relationship-derived-virtual-properties) for calculation instead of an `onRetrieve` script are not recalculated by `executeOnRetrieve`. These properties are recalculated only when there is a change (such as adding or removing a role affecting `effectiveRoles`, or a temporal constraint being triggered or changed). |