Advanced querying of KPS in Oracle API Gateway

The views expressed on this blog post are my own and do not necessarily reflect the views of Oracle

The standard and documented way to query a KPS is to query the primary key column. This is normally done with a selector expression like this, from the OAG documentation.

${kps.CustomerProfiles[JoeBloggs].age}

This expression retrieves the the age column on the KPS table CustomerProfiles with JoeBloggs for the value in the primary key column.

This is fine in many cases where you always have the primary key. But in some cases you have the need to query other columns in the table. This is possible but not using a selector expression.

By using a script filter you can query other columns. The workings of the example is explained with code comments.

importPackage(Packages.com.vordel.kps.impl);
importPackage(Packages.com.vordel.kps.query);

function invoke(msg)
{ 
// Get an attribute that we use for querying var nameAttribute= msg.get("nameAttribute");

// Get the KPS table with alias "User" var appData= KPS.getInstance().getModel().getAliases().get("AppData");

//Create a KeyQuery, the first argument is the column name, the second is the value that is queried for var query = new KeyQuery("name", nameAttribute);

//Preform the query 
var appDataRow = appData.getCached(query);

//Add the row as a attribute
msg.put("appDataRow", appDataRow );

return true;
}

The retrieved row can now be reached with a selector expression. The following retrieves the age column of the row.

${appDataRow.age}