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 , prototype.joinLeftOuter , and prototype.joinFullOuter , and 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();