Scoping queries in the Optic API

• 28 July, 2022
• By Dave Cassel
• No Comments

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 = myKeys.map(item => item.code);
let result = op
  .fromLiterals(myKeys)
  .joinLeftOuter(
    op
      .fromView("4V", "Sample")
      .where(cts.jsonPropertyValueQuery("keyProp", myKeys)),
    op.on(op.col("code"), op.col("keyCode"))
  )
  .limit(100)
  .result()
  .toArray();

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

response

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!

Every now and then I write an Optic query that has parts that look redundant. In the example below, assume...