How do I search managed user objects for attributes stored in arrays in IDM (All versions)?
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:
- Searching managed user objects for attributes stored in arrays (IDM 6 and later)
- Searching managed user objects for attributes stored in arrays (IDM 5.x)
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:
- Edit the repo.jdbc.json file (located in the /path/to/idm/conf directory) and
add a new query for your array:
- 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}", [...] ]
- 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}", [...] ]
- 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 }, [...] }
- Restart the IDM instance.
- 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®ion=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®ion=West
Searching managed user objects for attributes stored in arrays (IDM 5.x)
To allow searches for attributes stored in arrays:
- Edit the repo.jdbc.json file (located in the /path/to/idm/conf directory) and
add a new query for your array:
- 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}", [...] ]
- 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}", [...] ]
- 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 }, [...] }
- Restart the IDM instance.
- 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®ion=WestSee Also
Related Training
N/A
Related Issue Tracker IDs
N/A