MarkLogic TDE to handle parallel nested arrays

Question

I'm trying to use TDE in MarkLogic to extract data stored in parallel (i.e., matched element-wise) arrays into tabular (SQL) rows but am unable to find the correct path expressions and syntax to do this, despite many attempts.

Here is what I tried:

'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]);

and here is the result (property2 is missing):

{
"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
}
}
}
]
}

Here is the result that I'm trying to acheive:

{
"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
}
}
}
]
}

Lastly, I find it odd that I update the TDE to do something similar for /metadata I do get the result that I'm looking for:

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

this yields:

{
"document1": [
{
"row": {
"schema": "schema_name", 
"view": "view_name", 
"data": {
"rownum": "1", 
"another_item": "foo", 
"item": 123456, 
"other_item": 987654
}
}
}
]
}```

MarkLogic TDE to handle parallel nested arrays

Answers (1)

Related Questions