How To
ForgeRock Identity Platform
Does not apply to Identity Cloud

How do I search managed user objects for attributes stored in arrays in IDM (All versions)?

Last updated May 19, 2021

The purpose of this article is to provide information on searching managed user objects for attributes that are stored in arrays in IDM. Attributes stored in arrays are not searchable by default.


1 reader recommends this article

Overview

Predefined queries for generic mappings were updated in IDM 6 for improved relationship performance as noted in the Release Notes › Important Changes to Existing Functionality (Changes to predefined queries). As of IDM 7.1, you can perform REST queries on properly configured array fields. This feature only works for genericMapping objects, although you can convert objects if required. See Object Modeling Guide › Queries on Object Array Properties (JDBC) for further information.

This article provides examples to demonstrate making the attributes stored within the regions array searchable, where the attribute values are North, East, South and West. By doing this, you can then search for users who belong to a specific region. The examples are version specific because of the predefined query changes in IDM 6:

Note

You can set searchableDefault to true in the repo.jdbc.json file instead of setting the searchable property for individual attributes; however, this has an impact on performance and it is recommended that you only set the searchable property for the attributes that should be searchable.

Searching managed user objects for attributes stored in arrays (IDM 6 and later)

To allow searches for attributes stored in arrays:

  1. Edit the repo.jdbc.json file (located in the /path/to/idm/conf directory) and add a new query for your array:
    1. Copy the for-username query: "queries" : {        "genericTables" : {             [...]             "for-userName" : "SELECT obj.objectid, obj.rev, obj.fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id WHERE prop.propkey='/userName' AND prop.propvalue = ${uid} AND objtype.objecttype = ${_resource}",             [...]             ]
    2. Paste this in and update it to refer to your regions array, for example: "queries" : {        "genericTables" : {             [...]             "by-regions" : "SELECT obj.objectid, obj.rev, obj.fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id WHERE prop.propkey LIKE '/regions%' AND prop.propvalue = ${region} AND objtype.objecttype = ${_resource}",             [...]             ]
  2. Update the repo.jdbc.json file to add /regions as a searchable field under properties in the managed/user section: "managed/user" : {                "mainTable" : "managedobjects",                 "propertiesTable" : "managedobjectproperties",                 "searchableDefault" : false,                 "properties" : {                     "/userName" : {                         "searchable" : true                     },                     [...]                     "/regions" : {                         "searchable" : true                     },                     [...]                 }
  3. Restart the IDM instance.
  4. Update a managed user with a value in the regions array to force the object to be re-written to the repository with the new searchable properties; this will cause the managedobjectproperties table to be updated.

Searching managed user objects

You can now perform a query on the attributes stored in the regions array using a REST call such as:

  • IDM 7 and later: $ curl -X GET -H "X-OpenIDM-Username: openidm-admin" -H "X-OpenIDM-Password: openidm-admin" -H "Accept-API-Version: resource=1.0" -H "Content-Type: application/json" http://localhost:8443/openidm/managed/user?_queryId=by-regions&region=West
  • Pre-IDM 7: $ curl -X GET -H "X-OpenIDM-Username: openidm-admin" -H "X-OpenIDM-Password: openidm-admin" -H "Content-Type: application/json" http://localhost:8443/openidm/managed/user?_queryId=by-regions&region=West

Searching managed user objects for attributes stored in arrays (IDM 5.x)

To allow searches for attributes stored in arrays:

  1. Edit the repo.jdbc.json file (located in the /path/to/idm/conf directory) and add a new query for your array:
    1. Copy the for-username query: "queries" : {        "genericTables" : {             [...]             "for-userName" : "SELECT fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id WHERE prop.propkey='/userName' AND prop.propvalue = ${uid} AND objtype.objecttype = ${_resource}",             [...]             ]
    2. Paste this in and update it to refer to your regions array, for example: "queries" : {        "genericTables" : {             [...]             "by-regions" : "SELECT fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id WHERE prop.propkey LIKE '/regions%' AND prop.propvalue = ${region} AND objtype.objecttype = ${_resource}",             [...]             ]
  2. Update the repo.jdbc.json file to add /regions as a searchable field under properties in the managed/user section: "managed/user" : {                "mainTable" : "managedobjects",                 "propertiesTable" : "managedobjectproperties",                 "searchableDefault" : false,                 "properties" : {                     "/userName" : {                         "searchable" : true                     },                     [...]                     "/regions" : {                         "searchable" : true                     },                     [...]                 }
  3. Restart the IDM instance.
  4. Update a managed user with a value in the regions array to force the object to be re-written to the repository with the new searchable properties; this will cause the managedobjectproperties table to be updated.

Searching managed user objects

You can now perform a query on the attributes stored in the regions array using a REST call such as:

$ curl -X GET -H "X-OpenIDM-Username: openidm-admin" -H "X-OpenIDM-Password: openidm-admin" -H "Content-Type: application/json" http://localhost:8443/openidm/managed/user?_queryId=by-regions&region=West

See Also

How do I maintain relationships for a managed user in IDM (All versions) using REST Patch operations?

Using the REST API in IDM

Object Modeling Guide › JDBC Database Table Configuration

Related Training

N/A

Related Issue Tracker IDs

N/A


Copyright and Trademarks Copyright © 2021 ForgeRock, all rights reserved.