op.on

op.on(
   leftCol as String,
   rightCol as String
) as ondef

Summary

Specifies an equijoin using one columndef each from the left and right rows. The result is used by the prototype.joinInner and prototype.joinLeftOuter functions.

Use op.viewCol or op.col if you need to identify columns in the two views that have the same column name.

The default collation for string values in a TDE template is codepoint. If you are having problems joining columns that use a different collation, you will need to change the TDE template to use a matching collation, or change the appropriate range indexes to use codepoint.

Example

// Calculate the total expenses for each employee and return results in order of employee number.

const op = require('/MarkLogic/optic');

const employees = op.fromView('main', 'employees');
const expenses = op.fromView('main', 'expenses');
const totalexpenses  = op.col('totalexpenses');
const Plan =
employees.joinInner(expenses, op.on(employees.col('EmployeeID'), expenses.col('EmployeeID')))   
   .groupBy(employees.col('EmployeeID'), ['FirstName', 'LastName', expenses.col('Category'),
    op.sum(totalexpenses, expenses.col('Amount'))])
   .orderBy('EmployeeID')
Plan.result();