How to handle empty array-type scalar in tde template

Question

I need to handle with tde templates some documents with sometimes lacks of data. But still need to display it in tables. But my templates have problems to return this documents with empty data.

 var doc1 = xdmp.toJSON(
    {
          "customer":{
            "Name": "Name1", 
            "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 doc2 = xdmp.toJSON(
        {
          "customer":{
            "Name": "Name2", 
            "Addr": [
            ]
          }
        });

  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",
              "nullable": true
            },
            {
              "name":"Addr1",
              "scalarType":"string",
              "val":"Addr1",
              "nullable": true
            },
            {
              "name":"Addr2",
              "scalarType":"string",
              "val":"Addr2",
              "nullable": true
            },
            {
              "name":"CityName",
              "scalarType":"string",
              "val":"CityName",
              "nullable": true
            },
            {
              "name":"StateProvCd",
              "scalarType":"string",
              "val":"StateProvCd",
              "nullable": true
            },
            {
              "name":"PostalCode",
              "scalarType":"string",
              "val":"PostalCode",
              "nullable": true
            }
          ]
        }
      ]
    }
  }
  );



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

For this data extract the response is:

{
  "document1": [
    {
      "row": {
        "schema": "Schemas",
        "view": "CustomerAddress",
        "data": {
          "rownum": "1",
          "CustomerName": "Name1",
          "AddrTypeCd": "MailingAddress",
          "Addr1": "911 FORBES AVE",
          "Addr2": "SUITE XXX",
          "CityName": "asdfasfd",
          "StateProvCd": "PA",
          "PostalCode": "15219"
        }
      }
    },
    {
      "row": {
        "schema": "Schemas",
        "view": "CustomerAddress",
        "data": {
          "rownum": "2",
          "CustomerName": "Name1",
          "AddrTypeCd": "OfficeAddress",
          "Addr1": "911 Watson AVE",
          "Addr2": "SUITE XXX",
          "CityName": "asdfasfd",
          "StateProvCd": "CT",
          "PostalCode": "15119"
        }
      }
    }
  ],
  "document2": []
}

But I need to get in response data for customers without addresses too, like this:

{   "document1": [
    {
      "row": {
        "schema": "Schemas",
        "view": "CustomerAddress",
        "data": {
          "rownum": "1",
          "CustomerName": "Name1",
          "AddrTypeCd": "MailingAddress",
          "Addr1": "911 FORBES AVE",
          "Addr2": "SUITE XXX",
          "CityName": "asdfasfd",
          "StateProvCd": "PA",
          "PostalCode": "15219"
        }
      }
    },
    {
      "row": {
        "schema": "Schemas",
        "view": "CustomerAddress",
        "data": {
          "rownum": "2",
          "CustomerName": "Name1",
          "AddrTypeCd": "OfficeAddress",
          "Addr1": "911 Watson AVE",
          "Addr2": "SUITE XXX",
          "CityName": "asdfasfd",
          "StateProvCd": "CT",
          "PostalCode": "15119"
        }
      }
    }   ],   "document2": [    {
      "row": {
        "schema": "Schemas",
        "view": "CustomerAddress",
        "data": {
          "rownum": "1",
          "CustomerName": "Name2"
        }
      }
    }  ] }

Do I need to create second view to handle Name from document and then join it with address view? Do that multiple joins will have impact on performance?

Answer 1

Since there is an array of items you should create a second template to express those rows. You will have a view for the main customer and a view for the addresses. It is ideal to have a unique key to join with as well. Once you have the two views you can use SQL or the Optic API (MarkLogic's Multi-Model Querying Library) to query and join.

{
    "template": {
        "description": "test template",
        "context": "customer",
        "rows": [
            {
                "schemaName": "Schemas",
                "viewName": "Customer",
                "columns": [
                    {
                        "name": "id",
                        "scalarType": "int",
                        "val": "./id"
                    },
                    {
                        "name": "CustomerName",
                        "scalarType": "string",
                        "val": "./Name"
                    }
                ]
            }
        ],
        "templates": [
            {
                "context": "./Addr",
                "rows": [
                    {
                        "schemaName": "Schemas",
                        "viewName": "CustomerAddr",
                        "viewLayout": "sparse",
                        "columns": [
                            {
                                "name": "customerId",
                                "scalarType": "int",
                                "val": "../../id"
                            },
                            {
                                "name": "AddrTypeCd",
                                "scalarType": "string",
                                "val": "AddrTypeCd",
                                "nullable": true
                            },
                            {
                                "name": "Addr1",
                                "scalarType": "string",
                                "val": "Addr1",
                                "nullable": true
                            },
                            {
                                "name": "Addr2",
                                "scalarType": "string",
                                "val": "Addr2",
                                "nullable": true
                            },
                            {
                                "name": "CityName",
                                "scalarType": "string",
                                "val": "CityName",
                                "nullable": true
                            },
                            {
                                "name": "StateProvCd",
                                "scalarType": "string",
                                "val": "StateProvCd",
                                "nullable": true
                            },
                            {
                                "name": "PostalCode",
                                "scalarType": "string",
                                "val": "PostalCode",
                                "nullable": true
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

Example of querying with Optic API

'use strict';

const op = require('/MarkLogic/optic')

let customer = op.fromView('Schemas', 'Customer');
let addr = op.fromView('Schemas', 'CustomerAddr');

customer.joinLeftOuter(addr, op.on(op.col('id'), op.col('customerId'))).limit(10).result()

Results

{
    "Schemas.Customer.id": 1,
    "Schemas.CustomerAddr.customerId": 1,
    "Schemas.Customer.CustomerName": "Name1",
    "Schemas.CustomerAddr.AddrTypeCd": "MailingAddress",
    "Schemas.CustomerAddr.Addr1": "911 FORBES AVE",
    "Schemas.CustomerAddr.Addr2": "SUITE XXX",
    "Schemas.CustomerAddr.CityName": "asdfasfd",
    "Schemas.CustomerAddr.StateProvCd": "PA",
    "Schemas.CustomerAddr.PostalCode": "15219"
}

{
    "Schemas.Customer.id": 1,
    "Schemas.CustomerAddr.customerId": 1,
    "Schemas.Customer.CustomerName": "Name1",
    "Schemas.CustomerAddr.AddrTypeCd": "OfficeAddress",
    "Schemas.CustomerAddr.Addr1": "911 Watson AVE",
    "Schemas.CustomerAddr.Addr2": "SUITE XXX",
    "Schemas.CustomerAddr.CityName": "asdfasfd",
    "Schemas.CustomerAddr.StateProvCd": "CT",
    "Schemas.CustomerAddr.PostalCode": "15119"
} 

{
    "Schemas.Customer.id": 2,
    "Schemas.CustomerAddr.customerId": null,
    "Schemas.Customer.CustomerName": "Name2",
    "Schemas.CustomerAddr.AddrTypeCd": null,
    "Schemas.CustomerAddr.Addr1": null,
    "Schemas.CustomerAddr.Addr2": null,
    "Schemas.CustomerAddr.CityName": null,
    "Schemas.CustomerAddr.StateProvCd": null,
    "Schemas.CustomerAddr.PostalCode": null
}

{
    "Schemas.Customer.id": 3,
    "Schemas.CustomerAddr.customerId": null,
    "Schemas.Customer.CustomerName": "Name3",
    "Schemas.CustomerAddr.AddrTypeCd": null,
    "Schemas.CustomerAddr.Addr1": null,
    "Schemas.CustomerAddr.Addr2": null,
    "Schemas.CustomerAddr.CityName": null,
    "Schemas.CustomerAddr.StateProvCd": null,
    "Schemas.CustomerAddr.PostalCode": null
}

Answer 2

If you do not have a natural join key, something like below can be added in both views..

{
 "name": "joinKey",
 "scalarType": "string",
 "val": "xdmp:node-uri(.) || '#' || fn:position()"
}

The response of Andrew still stays correct. What is needed is to create TWO views and that is how embedded arrays can be flattened using TDEs. You can do quick testing using SQL statements too as below

select * from Schemas.Customer cus
LEFT  JOIN Schemas.CustomerAddr addr ON cus.joinKey = addr.joinKey

and

select * from Schemas.Customer cus,
Schemas.CustomerAddr addr 
where cus.joinKey = addr.joinKey

Answer 3

With a little xPath magic and some TDE template settings, you can actually do this with a nested template and a sparse definition.

Main template

• template for customers with no address
• template for customers with addresses

Please note: this would work with a context of / for documents in the system. However, for that, you need a directory or collection scope. This cannot be spoofed online, so I added root element to allow this to work as a sample without saving the documents. You could save the documents as you defined them (no root) as long as you also scoped them with either a directory or collection.

 var doc1 = xdmp.toJSON(
{
    "root" : {
      "customer":{
        "Name": "Name1", 
        "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 doc2 = xdmp.toJSON(
  {"root": 
     {
      "customer":{
        "Name": "Name2", 
        "Addr": [
        ]
      }
  }     
    });

var rowtde1 = xdmp.toJSON(
{
"template":{
  "context":"/root",
  "templates" : [
    {
      "context":"customer[fn:count(./Addr/*) = 0]/Name",
        "rows":[
          {
            "schemaName":"Schemas",
            "viewLayout": "sparse",
            "viewName":"CustomerAddress",
            "columns":[
              {
                "name":"CustomerName",
                "scalarType":"string",
                "val":"."
              }
            ]
          }
        ]
    },
            {
      "context":"customer[fn:count(./Addr/*) > 0]/Addr",
        "rows":[
          {
            "schemaName":"Schemas",
            "viewLayout": "sparse",
            "viewName":"CustomerAddress",
            "columns":[
              {
                "name":"CustomerName",
                "scalarType":"string",
                "val":"../../Name"
              },
              {
                "name":"AddrTypeCd",
                "scalarType":"string",
                "val":"AddrTypeCd",
                "nullable": true
              },
              {
                "name":"Addr1",
                "scalarType":"string",
                "val":"Addr1",
                "nullable": true
              },
              {
                "name":"Addr2",
                "scalarType":"string",
                "val":"Addr2",
                "nullable": true
              },
              {
                "name":"CityName",
                "scalarType":"string",
                "val":"CityName",
                "nullable": true
              },
              {
                "name":"StateProvCd",
                "scalarType":"string",
                "val":"StateProvCd",
                "nullable": true
              },
              {
                "name":"PostalCode",
                "scalarType":"string",
                "val":"PostalCode",
                "nullable": true
              }
            ]
          }
        ]
    }
  ]
}
}
);
tde.nodeDataExtract([doc1, doc2],[rowtde1]);

The result for the Name-only row is just the single value. This is allowed with sparse templates. You could also have defined the entire list of columns as null, but I do not see the value.

{
"document1": [{
        "row": {
            "schema": "Schemas",
            "view": "CustomerAddress",
            "data": {
                "rownum": "1",
                "CustomerName": "Name1",
                "AddrTypeCd": "MailingAddress",
                "Addr1": "911 FORBES AVE",
                "Addr2": "SUITE XXX",
                "CityName": "asdfasfd",
                "StateProvCd": "PA",
                "PostalCode": "15219"
            }
        }
    },
    {
        "row": {
            "schema": "Schemas",
            "view": "CustomerAddress",
            "data": {
                "rownum": "2",
                "CustomerName": "Name1",
                "AddrTypeCd": "OfficeAddress",
                "Addr1": "911 Watson AVE",
                "Addr2": "SUITE XXX",
                "CityName": "asdfasfd",
                "StateProvCd": "CT",
                "PostalCode": "15119"
            }
        }
    }
],
"document2": [{
    "row": {
        "schema": "Schemas",
        "view": "CustomerAddress",
        "data": {
            "rownum": "1",
            "CustomerName": "Name2"
        }
    }
}]
}