Define and Call Data Queries

An advanced query model enables you to define queries and to call them over the REST or Resource API. Three types of queries are supported, on both managed, and system objects:

Common filter expressions
Parameterized, or predefined queries
Native query expressions

Each of these mechanisms is discussed in the following sections.

Tip

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

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.

For a list of supported filter operations, see "Construct Queries".

Note that using common filter expressions to retrieve values from arrays is currently not supported. If you need to search within an array, you should set up a predefined (parameterized) in your repository configuration. For more information, see "Parameterized Queries".

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.

Important

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"

Note

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 params = {
    '_queryFilter' : 'givenName co "' + sourceCriteria + '" or ' + 'sn co "' + sourceCriteria + '"'
};
var results = openidm.query("system/ScriptedSQL/account", params)

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

Note

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.

Note

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.