Scoping queries in the Optic API

Every now and then I write an Optic query that has parts that look redundant. In the example below, assume the 4V.Sample view has a “keyCode” field and that the JSON docs the view is built from have a “keyProp” property. My goal is to gather some information from a view for a specific set of values. The set of values is itself the result of a calculation.

// Get an array of objects that include a "code" property. 
// These are the interesting ones to use in our query.
const myKeys = buildKeys();
// Make an array of just the code values
let myKeyCodes = => item.code);
let result = op
      .fromView("4V", "Sample")
      .where(cts.jsonPropertyValueQuery("keyProp", myKeys)),
    op.on(op.col("code"), op.col("keyCode"))

let response = {
  time: xdmp.elapsedTime(),


Notice the .where clause. This is a scoping query that narrows down the possibilities to consider in the rest of the query. Of course, with the joinLeftOuter, it’s redundant — we’ll get the same results with or without that .where, because we’re only going to produce one row for each row on the left (the literals).

So why bother? Performance.

I ran this query multiple times with and without the .where clause on a data set with about 750,000 rows in the 4V.Sample view. Without the clause, it took just short of 3 seconds to run. With the clause, it was about 0.1 seconds. Big difference!

Leave a Reply

Your email address will not be published. Required fields are marked *