如何使用MarkLogic TDE提取并行嵌套数组为SQL表格行？

我尝试使用MarkLogic中的TDE将存储在并行（即元素匹配）数组中的数据提取为表格（SQL）行，但多次尝试后仍未找到正确的路径表达式和语法。

尝试代码 #

'use strict';

let json = xdmp.toJSON({
"metadata": {
  "item": ["123456"], 
  "other_item": [987654],
  "another_item": "foo",
  }, 
"content": {
  "a": {
    "b": {
      "data": {
        "property1": [1.0, 2.0, 3.0, 4.0],
        "property2": [15.0, 100.0, 75.5, 50.5],
        "property3": [0.01, 0.1, 1, 10.0]
      }
    }
  }
}
});


let tpl = xdmp.toJSON({
  "template":{
    "context":"/content/a/b/data/array-node()/property1",
    "enabled" : true,
    "rows":[
      {
        "schemaName":"schema_name",
        "viewName":"view_name",
        "columns":[
          {
            "name":"another_item",
            "scalarType":"anyURI",
            "val":"/metadata/another_item",
            "nullable":true,
            "invalidValues": "ignore"
          }
          ,
          {
            "name":"property1",
            "scalarType":"double",
            "val":".",
            "nullable":true,
            "invalidValues": "ignore"
          }
          ,
          {
            "name":"property2",
            "scalarType":"double",
            "val":"../property2",
            "nullable":true,
            "invalidValues": "ignore"
          }
        ]
      }
    ]
  }
});

tde.validate([tpl]);
tde.nodeDataExtract([json], [tpl]);

当前结果（property2缺失） #

{
"document1": [
{
"row": {
"schema": "schema_name", 
"view": "view_name", 
"data": {
"rownum": "1", 
"another_item": "foo", 
"property1": 1
}
}
}, 
{
"row": {
"schema": "schema_name", 
"view": "view_name", 
"data": {
"rownum": "2", 
"another_item": "foo", 
"property1": 2
}
}
}, 
{
"row": {
"schema": "schema_name", 
"view": "view_name", 
"data": {
"rownum": "3", 
"another_item": "foo", 
"property1": 3
}
}
}, 
{
"row": {
"schema": "schema_name", 
"view": "view_name", 
"data": {
"rownum": "4", 
"another_item": "foo", 
"property1": 4
}
}
}
]
}

期望结果 #

{
"document1": [
{
"row": {
"schema": "schema_name", 
"view": "view_name", 
"data": {
"rownum": "1", 
"another_item": "foo", 
"property1": 1,
"property2": 15
}
}
}, 
{
"row": {
"schema": "schema_name", 
"view": "view_name", 
"data": {
"rownum": "2", 
"another_item": "foo", 
"property1": 2,
"property2": 100
}
}
}, 
{
"row": {
"schema": "schema_name", 
"view": "view_name", 
"data": {
"rownum": "3", 
"another_item": "foo", 
"property1": 3,
"property2": 75.5
}
}
}, 
{
"row": {
"schema": "schema_name", 
"view": "view_name", 
"data": {
"rownum": "4", 
"another_item": "foo", 
"property1": 4,
"property2": 50.5
}
}
}
]
}

关键问题与解决方案 #

问题出在模板的context路径和列的val表达式上。当context设为/content/a/b/data/array-node()/property1时，每个上下文节点是property1数组中的单个元素，此时../property2指向的是property1数组的兄弟节点property2数组本身，而非对应索引的元素。

正确做法是将context定位到数组元素的位置索引，再通过位置关联并行数组的对应元素。修改后的模板如下：

let tpl = xdmp.toJSON({
  "template":{
    "context":"/content/a/b/data/property1/array-node()",
    "enabled" : true,
    "rows":[
      {
        "schemaName":"schema_name",
        "viewName":"view_name",
        "columns":[
          {
            "name":"another_item",
            "scalarType":"anyURI",
            "val":"/metadata/another_item",
            "nullable":true,
            "invalidValues": "ignore"
          },
          {
            "name":"property1",
            "scalarType":"double",
            "val":".",
            "nullable":true,
            "invalidValues": "ignore"
          },
          {
            "name":"property2",
            "scalarType":"double",
            "val":"/content/a/b/data/property2/array-node()[position() = position(current())]",
            "nullable":true,
            "invalidValues": "ignore"
          },
          {
            "name":"property3",
            "scalarType":"double",
            "val":"/content/a/b/data/property3/array-node()[position() = position(current())]",
            "nullable":true,
            "invalidValues": "ignore"
          }
        ]
      }
    ]
  }
});

解释 #

1. Context路径调整：将context改为/content/a/b/data/property1/array-node()，此时每个上下文节点是property1数组里的单个元素，同时保留其在数组中的位置信息。
2. 并行数组关联：用position(current())获取当前上下文元素在property1数组中的位置，再用该位置匹配property2、property3数组中对应位置的元素，确保并行数组元素一一对应。

另外，之前针对metadata的操作能成功，是因为item和other_item都是metadata下的数组，上下文定位到item数组元素后，../../other_item指向other_item数组，而当数组只有一个元素时，TDE会自动提取该元素；但数组有多个元素时，必须通过位置索引关联。

内容的提问来源于stack exchange，提问作者tlicquia