TDE Scalar Type for Array

Question

I'm trying to retrun a JSON Array object as a column in TDE. What should be the scalar type?

I'm using MarkLogic 10.0-1. My JSON document is in nested structure and I want to expose row view using TDE with fields containing an array object. I tried using scalarType as string but tde.nodeDataExtract errors out. Is there a way to overcome this?

Given below contains sample document and template

    var doc1 = xdmp.toJSON(
      {
        "customer":{
          "Name": "afgasdfasfasffasf", 
          "Addr": [
            {
              "AddrTypeCd": "MailingAddress", 
              "Addr1": "911 FORBES AVE", 
              "Addr2": "SUITE XXX", 
              "CityName": "asdfasfd", 
              "StateProvCd": "PA", 
              "PostalCode": "15219"
            }, 
            {
              "AddrTypeCd": "OfficeAddress", 
              "Addr1": "911 Watson AVE", 
              "Addr2": "SUITE XXX", 
              "CityName": "asdfasfd", 
              "StateProvCd": "CT", 
              "PostalCode": "15119"
            }
          ]
        }
      });

    var rowtde1 = xdmp.toJSON(
    {
      "template":{
        "context":"/customer",
        "rows":[
          {
            "schemaName":"Schemas",
            "viewName":"SampleCustomer",
            "columns":[
              {
                "name":"CustomerName",
                "scalarType":"string",
                "val":"Name"
              },
              {
                "name":"Addr",
                "scalarType":"string",
                "val":"Addr"
              }
            ]
          }
        ]
      }
    }
    );



tde.nodeDataExtract([doc1],[rowtde1]);

Execution Error:

[javascript] TDE-EVALFAILED: 
tde.nodeDataExtract([Document({"customer":{"Name":"afgasdfasfasffasf", 
"Addr":[{"AddrTypeCd":"MailingAddress", ...}, ...]}})], 
[Document({"template":{"context":"/customer", "rows": 
[{"schemaName":"Schemas", ...}]}})]) -- Eval for Column Addr='Addr' 
returns multiple values (only one is expected)

Dave Cassel Dave Cassel · Accepted Answer · 2019-07-12T12:46:29

I think a slightly different approach will get you where you want to go. Let's change the context from /customer to /customer/Addr. Now TDE can work to extract one row for each item in the array. (If you had a bunch of data you wanted to extract that isn't in the array, you'd use more than one template to populate the index.

 var doc1 = xdmp.toJSON(
      {
        "customer":{
          "Name": "afgasdfasfasffasf", 
          "Addr": [
            {
              "AddrTypeCd": "MailingAddress", 
              "Addr1": "911 FORBES AVE", 
              "Addr2": "SUITE XXX", 
              "CityName": "asdfasfd", 
              "StateProvCd": "PA", 
              "PostalCode": "15219"
            }, 
            {
              "AddrTypeCd": "OfficeAddress", 
              "Addr1": "911 Watson AVE", 
              "Addr2": "SUITE XXX", 
              "CityName": "asdfasfd", 
              "StateProvCd": "CT", 
              "PostalCode": "15119"
            }
          ]
        }
      });

    var rowtde1 = xdmp.toJSON(
    {
      "template":{
        "context":"/customer/Addr",
        "rows":[
          {
            "schemaName":"Schemas",
            "viewName":"CustomerAddress",
            "columns":[
              {
                "name":"CustomerName",
                "scalarType":"string",
                "val":"../../Name"
              },
              {
                "name":"AddrTypeCd",
                "scalarType":"string",
                "val":"AddrTypeCd"
              },
              {
                "name":"Addr1",
                "scalarType":"string",
                "val":"Addr1"
              },
              {
                "name":"Addr2",
                "scalarType":"string",
                "val":"Addr2"
              },
              {
                "name":"CityName",
                "scalarType":"string",
                "val":"CityName"
              },
              {
                "name":"StateProvCd",
                "scalarType":"string",
                "val":"StateProvCd"
              },
              {
                "name":"PostalCode",
                "scalarType":"string",
                "val":"PostalCode"
              }
            ]
          }
        ]
      }
    }
    );



tde.nodeDataExtract([doc1],[rowtde1]);

This gets you one row for each address added to the Schemas.CustomerAddress view.

Adding info based on this comment: "I'm trying to avoid Joins as well as multiple views to build and maintain. Every new template, has a cost on the server due to indexing."

It's worth remembering that MarkLogic views are implemented differently from views in a relational database. Under the hood, MarkLogic's views are implemented using the triple index, so there will be joins regardless. The approach I laid out above is the commonly used way to handle this situation.

TDE Scalar Type for Array

1 Answers