Written Tutorial

Problem

Select documents based on criteria that is located in different documents. You have Template Driven Extraction (TDE) templates that extract data from both document types into views.

Solution

Applies to MarkLogic versions 9+

This recipe assumes that you have two types of documents: one describing factories and one describing events (problems) that happen at those factories. A TDE template has extracted a factory ID and the state where the factory is located into the factories view of the manufacturing schema. Another template has extracted columns from the events documents: factoryFKey (factory foreign key), severity (of the event), and status (‘Active’ or ‘Resolved’). These columns are in the events view.

const op = require('/MarkLogic/optic');
​
const docId = op.fragmentIdCol('docId');
​
op.fromView('manufacturing', 'events')
  .where(
    op.and(
      op.eq(op.col('severity'), 1),
      op.eq(op.col('status'), 'Active')
    ))
  .groupBy('factoryFKey')
  .joinInner(
    op.fromView('manufacturing', 'factories', null, docId),
    op.on('factoryFKey', 'factoryID')
  )
  .joinDoc('doc', docId)
  .select('doc')
  .result();

import module namespace op="https://marklogic.com/optic"
  at "/MarkLogic/optic.xqy";

let $doc-id := op:fragment-id-col('docId')
return
  op:from-view('manufacturing', 'events')
  => op:where(
    op:and(
      op:eq(op:col('severity'), 1),
      op:eq(op:col('status'), 'Active')
    ))
  => op:group-by('factoryFKey')
  => op:join-inner(
    op:from-view('manufacturing', 'factories', (), $doc-id),
    op:on('factoryFKey', 'factoryID')
  )
  => op:join-doc('doc', $doc-id)
  => op:select('doc')
  => op:result()

Required Indexes: row index populated with two views

Discussion

The desired result from this query is the set of factory documents that have a severity 1, active event going on. The severity and status are in the events view, so we need to join the views to get to the information we want.

The query starts with the events view, as that is where the filtering criteria are found. The where clause limits the rows that we need to work with prior to doing the join, which is based on the factory ID columns. This early scoping is important for performance.

Once the selection is made and the join between the views is done, we join with the documents. Notice that as part of the joinInner, the op.fromView that connects to the factories view refers to docId. This is how the joinDoc call knows which document to retrieve.

The final step is to select the doc column and return just that. Optic will return a Sequence, which your code can then iterate over.