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

For limits on queries in progressive profiling, see Custom Progressive Profile Conditions.

Queries on object array properties (JDBC)

Support for queries on object array properties requires the following:

A JDBC repository with generic object mapping. Queries on arrays are not supported with explicit mappings. If you need to convert from explicitly mapped objects to generic, see Convert an Explicit Mapped Object to a Hybrid Mapped Object (JDBC).
For PostgreSQL only, you must configure array fields. Additional information about PostgreSQL JSON functions.

For JDBC repositories other than PostgreSQL, the array property must be configured as searchable. 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:

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:

"correlationQuery" : {
  "type" : "text/javascript",
  "source" : "var qry = {'_queryFilter': org.forgerock.util.query.QueryFilter.equalTo('uid', source.userName).toString()}; qry"
}

Common filter expressions

The ForgeRock 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:

openidm.query("managed/user", { "_queryFilter" : '/userName eq "smith"' });

Note that, this JavaScript invocation is internal and is not subject to the same URL-encoding requirements that a GET request would be. Also, because JavaScript supports the use of single quotes, it is not necessary to escape the double quotes in this example.

Parameterized queries

You can access managed objects in JDBC repositories using custom parameterized queries. Define these queries in your JDBC repository configuration, (repo.*.json), and call them by their _queryId.

Parameterized queries are not supported for system objects, or for DS repositories.
All internal queries are filtered queries. Internal queries that reference a queryId are translated to filtered queries.

A typical query definition is as follows:

"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:

?_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 is not returned in query results, even if requested. For more information about relationships, see Relationships between objects.

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:

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, see Improving Generic Mapping Search Performance (JDBC).

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:

Equal a specified value

This is the associated JSON comparison expression: json-pointer eq json-value.

Example 1

"_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

"_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.

Contain a specified value

This is the associated JSON comparison expression: json-pointer co json-value.

Example

"_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
}

Start with a specified value

This is the associated JSON comparison expression: json-pointer sw json-value.

Example

"_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
}

Are less than a specified value

This is the associated JSON comparison expression: json-pointer lt json-value.

Example

"_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
}

Are less than or equal to a specified value

This is the associated JSON comparison expression: json-pointer le json-value.

Example

"_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
}

Are greater than a specified value

This is the associated JSON comparison expression: json-pointer gt json-value

Example

"_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
}

Are greater than or equal to a specified value

This is the associated JSON comparison expression: json-pointer ge json-value.

Example

"_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:

"_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. You can use this clause for queries on singleton string properties or arrays. in query expressions are not supported through the admin UI or for use by delegated administrators.

The in operator is shorthand for multiple OR conditions.

The following example command includes escaped characters. For readability, the non-escaped URL syntax is:

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 ForgeRock Directory Services (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, see Indexes 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, 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).

IDM 7.2.1

Define and call data queries

Queries on object array properties (JDBC)

Special characters in queries

Common filter expressions

Parameterized queries

Native query expressions

Construct queries

Comparison expressions

Presence expressions

Literal expressions

In expression clause

Filter expanded relationships

Complex expressions

Filter objects in arrays

Page query results

Sort query results

Recalculate virtual property values in queries

`In` expression clause