--- title: Define and call data queries description: An advanced query model enables you to define queries and to call them over the REST or Resource API. The following types of queries are supported, on both managed, and system objects: component: pingidm version: 8.1 page_id: pingidm:objects-guide:queries canonical_url: https://docs.pingidentity.com/pingidm/8.1/objects-guide/queries.html keywords: ["Data Object Model", "REST API", "JDBC", "Virtual Properties"] section_ids: query-array-reqs: Queries on object array properties (JDBC) special-char-queries: Special characters in queries query-filters: Common filter expressions parameterized-queries: Parameterized queries parameterized-queries-queryid-entries: queryId entries parameterized-queries-query-filters: queryFilters native-queries: Native query expressions 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 virtual property values in queries --- # Define and call data queries An advanced query model enables you to define queries and to call them over the REST or Resource API. The following types of queries are supported, on both managed, and system objects: * Common filter expressions * Parameterized, or predefined queries * Native query expressions ## Queries on object array properties (JDBC) Support for queries on object array properties requires the following: * A JDBC repository with [generic object mapping](explicit-generic-mapping-jdbc.html#generic-mappings-jdbc). Queries on arrays are not supported with explicit mappings. If you need to convert from explicitly mapped objects to generic, refer to [Convert an Explicit Mapped Object to a Hybrid Mapped Object (JDBC)](explicit-generic-mapping-jdbc.html#convert-explicit-to-hybrid-jdbc). * For PostgreSQL only, you must [configure array fields](../install-guide/repository-postgresql.html#postgres-conf-search-array). [Additional information about PostgreSQL JSON functions](https://www.postgresql.org/docs/9.5/functions-json.html). * For JDBC repositories other than PostgreSQL, the array property must be [configured as searchable](explicit-generic-mapping-jdbc.html#generic-mappings-jdbc). If you add additional properties as searchable after the initial install/migration of IDM, run the `/path/to/openidm/bin/defaults/script/update/rewriteObjects.js` script, specifying the new `objectPaths` of properties to make searchable: ```none curl \ --header "Content-Type: application/json" \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header 'X-OpenIDM-NoSession: true' \ --request POST \ --data-raw '{ "type": "text/javascript", "file": "/path/to/openidm/bin/defaults/script/update/rewriteObjects.js", "globals": { "rewriteConfig": { "queryFilter": "true", "pageSize": 1000, "objectPaths": [ "repo/config", "repo/internal/usermeta", "repo/managed/role", "repo/managed/user", "repo/reconprogressstate", "repo/relationships", "repo/scheduler/triggers" ] } } }' \ "http://localhost:8080/openidm/script/?_action=eval" ``` * Do not use array fields in a `sortKey`. ## Special characters in queries JavaScript query invocations are not subject to the same URL-encoding requirements as GET requests. Because 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" } ``` ## Common filter expressions The Ping 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 in that they do not require knowledge of how the object is stored and 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 "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ 'http://localhost:8080/openidm/managed/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/user", { "_queryFilter" : '/userName eq "smith"' }); ``` | | | | - | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | This JavaScript invocation is internal and isn't subject to the same URL-encoding requirements that a GET request would be. Because JavaScript supports the use of single quotes, it's not necessary to escape the double quotes in the preceding example. | ## Parameterized queries You can access managed objects in JDBC repositories using custom parameterized queries through [queryId entries](#parameterized-queries-queryid-entries). Define these queries in your JDBC repository configuration in `repo.*.json` and call them by their `_queryId`. | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | For system objects or for PingDS repositories, parameterized queries are defined outside of the JDBC repository configuration using [query filters](#parameterized-queries-query-filters) in the `queryFilters.json` file in your project's `conf` directory. | ### queryId entries A typical query definition is as follows: ```sql "query-all-ids" : "SELECT objectid FROM ${_dbSchema}.${_table} LIMIT ${int:_pageSize} OFFSET ${int:_pagedResultsOffset}", ``` To call this query, you would reference its ID, as follows: ```javascript ?_queryId=query-all-ids ``` The following example calls `query-all-ids` over the REST interface: ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ "http://localhost:8080/openidm/managed/user?_queryId=query-all-ids" ``` | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | | In `repo.jdbc.json`, the `queries` configuration object has a property, `validInRelationshipQuery`, which is an array specifying the IDs of queries that use relationships. If you define parameterized queries that you expect to use as part of a relationship query, you must add the query ID to this array. If no query IDs are specified or if the property is absent, relationship information isn't returned in query results, even if requested. Learn more in [Configuring relationships](relationships.html). | ### queryFilters To define parameterized queries outside of JDBC repositories, use the `queryFilters.json` file in your project's `conf` directory. This configuration file also allows customers to define additional `queryId` to `queryFilter` mappings and to change the authentication queries for customized authentication attributes related to `queryIds`. Learn more in [Attributes used for authentication](../auth-guide/authenticating-users.html#authentication-attributes). The following queries demonstrate using `queryFilters.json`: ```json { "credential-query" : { "_queryFilter" : "/userName eq \"${username}\" AND /accountStatus eq \"active\"", "_fields" : [ "sn" ] }, "credential-internaluser-query" : { "_queryFilter" : "/_id eq \"${username}\"", "_fields" : [ "sn" ] }, "for-userName" : { "_queryFilter" : "/userName eq \"${uid}\"", "_fields" : [ "sn" ] } } ``` ## Native query expressions Native query expressions are supported for system objects only, and can be called directly. You should only use native queries in situations where common query filters or parameterized queries are insufficient. For example, native queries are useful if the query needs to be generated dynamically. The query expression is specific to the target resource and uses the native query language of that system resource. Native queries are made using the `_queryExpression` keyword. ## 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 on which the query should be performed, and `params` provides the parameters that are 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/user", queryParams) ``` Over the REST interface, the query filter is specified as `_queryFilter=filter`, for example: ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'http://localhost:8080/openidm/managed/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. For generic mappings, any fields that are included in the query filter (for example `userName` in the previous query), must be explicitly defined as *searchable*, if you have set the global `searchableDefault` to false. For more information, refer to [Improving Generic Mapping Search Performance (JDBC)](explicit-generic-mapping-jdbc.html#searches-with-generic-mappings). 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 "X-OpenIDM-Username: openidm-admin" \ > --header "X-OpenIDM-Password: openidm-admin" \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'http://localhost:8080/openidm/managed/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 "X-OpenIDM-Username: openidm-admin" \ > --header "X-OpenIDM-Password: openidm-admin" \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'http://localhost:8080/openidm/managed/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 user name and given name of all managed users whose first name (`givenName`) contains "Da": > > ``` > curl \ > --header "X-OpenIDM-Username: openidm-admin" \ > --header "X-OpenIDM-Password: openidm-admin" \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'http://localhost:8080/openidm/managed/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 "X-OpenIDM-Username: openidm-admin" \ > --header "X-OpenIDM-Password: openidm-admin" \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'http://localhost:8080/openidm/managed/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 "X-OpenIDM-Username: openidm-admin" \ > --header "X-OpenIDM-Password: openidm-admin" \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'http://localhost:8080/openidm/managed/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 "X-OpenIDM-Username: openidm-admin" \ > --header "X-OpenIDM-Password: openidm-admin" \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'http://localhost:8080/openidm/managed/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 "X-OpenIDM-Username: openidm-admin" \ > --header "X-OpenIDM-Password: openidm-admin" \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'http://localhost:8080/openidm/managed/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 "X-OpenIDM-Username: openidm-admin" \ > --header "X-OpenIDM-Password: openidm-admin" \ > --header "Accept-API-Version: resource=1.0" \ > --request GET \ > 'http://localhost:8080/openidm/managed/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 "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'http://localhost:8080/openidm/managed/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 "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'http://localhost:8080/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 "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'http://localhost:8080/openidm/managed/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 admin UI or for use by [delegated administrators](../auth-guide/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 http://localhost:8080/openidm/managed/user?_pageSize=1000&_fields=userName&_queryFilter=/userName in '["user4a","user3a"]' ``` | ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request GET \ "http://localhost:8080/openidm/managed/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 directly filter expanded relationships from a collection, such as `authzRoles`. The following example queries the `manager-int` authorization role of a user: ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request GET \ "http://localhost:8080/openidm/managed/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 } ``` | | | | - | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | You can use `_queryFilter` on fields within `_refProperties` when using DS as your repository. This functionality is not available if you are using a JDBC repository. | ### 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 last name Jensen: ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'http://localhost:8080/openidm/managed/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 "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'http://localhost:8080/openidm/managed/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/role", "_refResourceId": "testManagedRole", "_ref": "managed/role/testManagedRole" } ] }, { "_id": "aca0042c-9f4c-4ad5-8cf7-aca0adeb3470", "_rev": "200bc5d6-7cc1-4648-a854-3137f3d9c103-1545", "userName": "jdoe", "sn": "Doe", "givenName": "John", "effectiveRoles": [ { "_refResourceCollection": "managed/role", "_refResourceId": "testManagedRole", "_ref": "managed/role/testManagedRole" } ] } ], "resultCount": 2, "pagedResultsCookie": null, "totalPagedResultsPolicy": "NONE", "totalPagedResults": -1, "remainingPagedResults": -1 } ``` | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | Because `curl` uses brackets (`[]`, `{}`) for processing, you need to escape your brackets with a `\`. This may be unnecessary in cases where you are using a different method to call IDM. | | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | | This syntax is only available when using DS or PostgreSQL as your repository.When using a PostgreSQL repository and querying an array, properties that are a string, boolean, number, or object are supported. However, arrays are not supported (you can't filter on an array within an array). | ## 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 effectively 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 "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request GET \ "http://localhost:8080/openidm/managed/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 PingDS (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 in which the JSON result is returned, based on this list. The `_sortKeys` parameter is not supported for predefined queries. | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | When using DS as a repo:- 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 in DS. - When viewing data that is persisted in DS 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).For more information about how to set up indexes in DS, refer to [Indexes](https://docs.pingidentity.com/pingds/8.1/config-guide/indexing.html) in the *DS Configuration Guide*. | The following query returns all users with the `givenName` Dan, and sorts the results alphabetically, according to surname (`sn`): ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'http://localhost:8080/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 actually a list of references to other objects, not the objects themselves. | ## Recalculate virtual property values in queries For managed objects IDM includes an `onRetrieve` script hook that enables you to recalculate property values 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 "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request GET \ 'http://localhost:8080/openidm/managed/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` 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). |