MarkLogic 12 EA 1 Product Documentationop: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)
:)