Loading TOC...
JavaScript ModifyPlan.prototype.unnestLeftOuter

MarkLogic 12 EA 2 Product Documentation
op:unnest-left-outer

op:unnest-left-outer(
   $plan as map:map,
   $inputColumn as item(),
   $valueColumn as item(),
   [$ordinalColumn as item()?]
) as map:map

Summary

This function flattens an array value into multiple rows.Then performs a op:join-left-outer on the rest of the rows.

Parameters
$plan The Optic Plan. You can either use the XQuery => chaining operator or specify the variable that captures the return value from the previous operation.
$inputColumn The input column, which contains an array, to flatten into rows. This can be a string of the column name or an op:col. Use op:view-col or op:schema-col if you need to identify columns in the two views that have the same column name.
$valueColumn The output column which contains the flattened array values. This can be a string of the column name or an op:col. Use op:view-col or op:schema-col as needed.
$ordinalColumn The ordinalColumn is optional. If specified, an additional column will be added to the rows of flattened array values, starting from 1. This can be a string of the column name or an op:col. Use op:view-col or op:schema-col as needed.

Example

xquery version "1.0-ml";

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

import module namespace ofn="http://marklogic.com/optic/expression/fn"
     at "/MarkLogic/optic/optic-fn.xqy";

op:from-view("unnestSchema", "unnestView")
   => op:bind((
       op:as("teamMemberSequence",  ofn:tokenize(op:col("teamMembers"),  ","))
       ))
   => op:unnest-left-outer('teamMemberSequence','teamMember', 'ordinality')
   => op:order-by(('department', 'ordinality'))
   => op:result()

(: This returns
[{"unnestSchema.unnestView.department":"CEO-Office","teamMemberSequence":null,"unnestSchema.unnestView.teamMembers":null,"teamMember":null,"ordinality":null},
{"unnestSchema.unnestView.department":"Engineering","teamMemberSequence":["Bob","Alice"],"unnestSchema.unnestView.teamMembers":"Bob,Alice","teamMember":"Bob","ordinality":1},
{"unnestSchema.unnestView.department":"Engineering","teamMemberSequence":["Bob","Alice"],"unnestSchema.unnestView.teamMembers":"Bob,Alice","teamMember":"Alice","ordinality":2},
{"unnestSchema.unnestView.department":"Marketing","teamMemberSequence":null,"unnestSchema.unnestView.teamMembers":"","teamMember":null,"ordinality":null},
{"unnestSchema.unnestView.department":"Sales","teamMemberSequence":["Robert","Cindy"],"unnestSchema.unnestView.teamMembers":"Robert,Cindy","teamMember":"Robert","ordinality":1},
{"unnestSchema.unnestView.department":"Sales","teamMemberSequence":["Robert","Cindy"],"unnestSchema.unnestView.teamMembers":"Robert,Cindy","teamMember":"Cindy","ordinality":2}]
:)

(: Use below JavaScript queries to insert template and data
// Insert a template
declareUpdate();
const tde = require("/MarkLogic/tde.xqy");
const template = xdmp.toJSON(
{
  "template":{
    "context":"office",
    "rows":[
      {
        "schemaName":"unnestSchema",
        "viewName":"unnestView",
        "columns":[
          {
            "name":"department",
            "scalarType":"string",
            "val":"department",
            "nullable":true,
            "invalidValues":"ignore"
          },
          {
            "name":"teamMembers",
            "scalarType":"string",
            "val":"teamMembers",
            "nullable":true,
            "invalidValues":"ignore"
          }
        ]
      }
    ]
  }
}
);
tde.templateInsert('/optic/unnest/unnestTemplate.json', template)

//insert a document
declareUpdate();
let doc =  {office:[
            {department:"Engineering", teamMembers: 'Bob,Alice'},
            {department:"Sales", teamMembers:'Robert,Cindy'},
            {department:"Marketing", teamMembers:""},
            {department:"CEO-Office", teamMembers:null}
]};
 xdmp.documentInsert('/optic/unnest/doc1.json', doc)
:)

Stack Overflow iconStack Overflow: Get the most useful answers to questions from the MarkLogic community, or ask your own question.