Creating Template Views

MarkLogic allows you to configure a template view. Each template specifies how different parts of a document can be projected as columns of a row in a view. These views can then be queried using SQL from a server-side program with xdmp:sql, mlsql, or ODBC. Template views are a simple, powerful way to specify a relational lens over documents, making parts of your document data accessible through SQL.

You can also query that view server-side using the Optic API, which is a fluent JavaScript and XQuery interface with the ability to perform joins and aggregates on views over documents. The Optic API gives developers idiomatic JavaScript and XQuery access to relational operations over rows, combined with rich document search. The Optic API is described in the Optic API for Multi-Model Data Access in Develop Server-Side Applications.

This section describes how to configure template views to model your document data for access as rows. Template views can be configured using the TDE API described in Validating and Inserting a Template.

The focus of this section is on the template elements that are specific to creating views. The Template Driven Extraction (TDE) section in Develop Server-Side Applications describes the template elements that are common to all types of data-extraction templates.

Indexing Views and Columns

Views generated using TDE templates are materialized at indexing time. These materialized views are then indexed for fast retrieval. For large datasets, this indexing may take some time to complete. Changes to an existing view like adding/removing columns, or changing datatype or collation, should be planned accordingly during off-peak hours. Until the reindex completes, the view may be unsearchable or incomplete depending on the reindexing setting (See: Availability of Columns During a Database Reindex Operation).

Views and columns can also be marked as "virtual". Virtual views are useful for supporting ad hoc queries and data exploration on large data sets where query needs are not known up front or are frequently changing. Unlike materialized views, virtual views are immediately searchable upon configuration. The extracted values are not persisted to disk, so they do not require additional storage. Column values are extracted at query time rather than at write time, so queries against virtual views may be slower than queries against materialized views. However, the query engine uses a number of techniques to leverage the universal index where possible to make the queries run as fast as possible.

Views may be declared virtual using viewVirtual property or the view-virtual element. Each column may be declared virtual using the virtual property or element, separately. A view can have a mix of virtual and materialized columns. Regardless of the virtual setting, these views can be searched using SQL, the Optic API, or the /v1/rows REST API endpoint.

Comparison of materialized, virtual, and mixed views

This table summarizes the key differences between materialized, virtual, and mixed views:

Using view-virtual and virtual settings as factors, views can be categorized in three ways, materialized, virtual, or mixed:

Template View Elements

A template view contains elements described in Template Elements. This section will focus on the rows element or property that configures a template view.

A template view contains these elements and their child elements:

<template
    xmlns="http://marklogic.com/xdmp/tde"
  >
  <context>...</context>
  <description>...</description>
  <enabled>...</enabled>
  <collections>...</collections>
  <directories>...</directories>
  <path-namespaces>...</path-namespaces>
  <vars>...</vars>
  <rows>...</rows>
</template>    

{
  "template": {
    "context": "...",
    "description": "...",
    "enabled": ...
    "collections": [...],
    "directories": [...],
    "pathNamespaces": [...],
    "vars": [...]
    "rows":[...]
  }
}

Row

<template
    xmlns="http://marklogic.com/xdmp/tde"
  >
  <context>...</context>
  ...
  <rows>
    <row>
      <schema-name>...</schema-name>
      <view-name>...</view-name>
      <view-layout>...</view-layout>
      <view-virtual>...</view-virtual>
      <columns>
        <column>...</column>
      </columns>
    </row>
  </rows>
</template>  

{
  "template": {
    "context": "...",
    ...
    "rows":[
      {
        "schemaName":"...",
        "viewName":"...",
        "viewLayout":"...",
        "viewVirtual":...,
        "columns":[{
          ...
        }]
      }
    ]
  }
}

This table describes each element or property that may be configured for a row:

Columns

<columns>
  <column>
    <name>...</name>
    <scalar-type>...</scalar-type>
    <val>...</val>
    <virtual></virtual>
    <nullable></nullable>
    <permissions>
      <role-name>...</role-name>
    </permissions>
    <default>...</default>
    <invalid-values>...</invalid-values>
    <reindexing>...</reindexing>
    <collation>...</collation>
    <dimension>...</dimension>
    <ann-compression>...</ann-compression>
    <ann-distance>...</ann-distance>
    <ann-indexed>...</ann-indexed>
  </column>
</columns>

{
  "columns":[
    {
      "name":"...",
      "scalarType":"...",
      "val":"...",
      "nullable":false,
      "permissions":[
        "..."
      ]
      "default":"...",
      "invalidValues":"...",
      "reindexing":"...",
      "collation":"...",
      "dimension":"...",
      "annCompression":"...",
      "annDistance":"...",
      "annIndexed":"...",
    }
  ]
}

This table describes each element or property that may be configured for a column:

Views and Non-Conforming Documents

For simplicity, this section assumes that the configured context matches a single element or property within a document. If your context matches multiple values, then wherever you see "document," think "context element or property."

Documents may be added before or after template creation. In either scenario, there may be documents that do not conform with the TDE column configuration. The settings discussed in this section define the rules on how to identify, when to identify and how to handle non-conforming documents.

How to identify a non-conforming document

Given this column configuration ...

  <column>
    <val>instance/Product/price</val>
    <nullable>false</nullable>
    <scalar-type>decimal</scalar-type>
    ...
  </column>

... where

• val specifies how data is extracted and projected as a value of the column.

• nullable specifies whether this column is allowed to have no value, or if a valid value is required.

• scalar-type specifies the type casting to be enforced for this column. The result of val must be castable to this data type to be considered valid.

... any document without a price element or property at the specified path, or without a value that can be cast to decimal is considered as a non-conforming document.

When to identify a non-conforming document

Identifying non-conforming documents is possible only when data is extracted. The virtual setting specifies when data is extracted. For columns with "virtual": false, the val, scalar-type, and nullable settings are enforced during indexing. For new documents, indexing happens before the transaction commits. For existing documents, index time happens during reindexing.

A setting of "virtual": true indicates that the val, scalar-type, and nullable settings are enforced at access time during the query. Access time is defined as the specific step during which a column is needed to resolve a query. So, for this query ...

op.fromView('acme', 'products')
  .where(op.gt(op.col('quantity'), 1))
  .select(['name', 'quantity'])
  .result()

... a document without a price element, or with a price element with an invalid value, is included in the result. The price column is never accessed. The column's settings are never enforced. The document is not identified as non-conforming. See Virtual Views and Non-Conforming Documents for further explanation.

How to handle non-conforming documents

Set invalidValues to one of these values to specify what MarkLogic Server does when encountering a non-conforming document:

• ignore: MarkLogic Server silently skips nonconforming documents during data extraction.

• reject: MarkLogic Server throws an error. For incoming non-conforming documents, this error prevents the documents from being written. For existing documents, this error causes them to be skipped during data extraction but does not delete the documents.

To record this error in the system error log, set the Group’s "file log level" to debug before you configure the template. See Understanding the Log Levels in Administrate MarkLogic Server.

This table summarizes the behavior for each setting:

Nullable columns and non-conforming documents

The previous subsection assumes nullable is set to false (default). If nullable is set to true, then invalidValues handling is relaxed for certain scenarios. The behavior is different for a missing value and an invalid value.

Missing means that the result of val is an empty sequence. Commonly, the provided path expression targets an element or property that is not included in the document, or that the JSON property has a null value. An empty XML element is considered an empty string, but an empty string is not considered missing.

An invalid value means that the target element or property of the configured path expression in val exists in the document, but the extracted value cannot be type-casted into the configured scalarType.

This table shows these behaviors along with the previous ones:

Default and nullable columns

A column’s default value can further enhance the behavior when nullable is set to true such that the previously "null column" will be replaced with the configured value for default. The configured default is ignored if nullable is set to false.

This table highlights this change:

Example Workspace on Non-conforming Documents

To better highlight the behavior discussed, we can run some code in Query Console. We begin by creating documents in the Documents database:

declareUpdate()

xdmp.documentInsert(
  "/missing.json", 
  {
    "content" : {
      "label": "missing"
    }
  }
) 

xdmp.documentInsert(
  "/invalid.json", 
  {
    "content" : {
      "label": "invalid",
      "count": "one"
    }
  }
) 

xdmp.documentInsert(
  "/valid.json", 
  {
    "content" : {
      "label": "valid",
      "count": 1
    }
  }
)

We then configure the template using this query:

'use strict';
declareUpdate();

const tde = require("/MarkLogic/tde.xqy");

let template = xdmp.toJSON({
  "template": {
    "description": "template to check non-conforming documents",
    "context": "/content",
    "rows": [
      {
        "schemaName": "acme",
        "viewName": "test",
        "columns": [
          {
            "name": "label",
            "scalarType": "string",
            "val": "label"
          },
          {
            "name": "count",
            "scalarType": "int",
            "val": "count"
          }
        ]
      }
    ]
  }
})
tde.templateInsert("Template.json", template)

We can then run this SQL statement to test the result after reindexing completes:

select * 
from test

At this time, the column count is not nullable (default when nullable is omitted). So, the previous query results only in a row containing label=valid.

Additionally, the column count is set to reject invalid values (default when invalidValues is omitted). So, trying to re-create documents /missing.json and /invalid.json fails.

We can modify the template so that count has nullable set to true:

{
  "name": "count",
  "scalarType": "int",
  "val": "count",
  "nullable": true
}

If we re-run our SQL select statement, then our result includes a row with label=missing. Notice that the value for column count is null.

Additionally, trying to re-create the document for /missing.json now succeeds. But trying to re-create the document for /invalid.json fails.

We can modify the template so that count has default set to -9:

{
  "name": "count",
  "scalarType": "int",
  "val": "count",
  "nullable": true,
  "default": "-9"
}

The value for default has to be a string even if the scalarType is int.

If we re-run our SQL select statement, then our result includes a row with label=missing. But instead of null, the value for column count is -9.

We can modify the template so that count has invalidValues set to ignore:

{
  "name": "count",
  "scalarType": "int",
  "val": "count",
  "nullable": true,
  "default": "-9",
  "invalidValues": "ignore"
}

If we re-run our SQL select statement, then our result includes a row with label=invalid with the value for column count set to -9.

Additionally, trying to re-create all documents now succeeds.

Summary of handling non-conforming documents

This table summarizes all the non-conforming document scenarios:

Virtual Views and Non-Conforming Documents

As described in When to identify a non-conforming document, enforcement of nullable and invalid-values happens when the involved column is accessed. So, if a nullable column is never referenced as part of the query, then a document may be included as part of the result set even if it would have been considered a non-conforming document when all columns are referenced.

Given this row configuration...

{
  ...
  "schemaName":"acme",
  "viewName":"employee",
  "viewVirtual": true,
  "columns":[
  {
    "name":"firstName",
    "val":"firstName",
    ...
  }, {
    "name":"lastName",
    "val":"lastName",
    ...
  }, {
    "name":"middleInitial",
    "val":"middleInitial",
    "nullable": false
    ...
  }, {
    "name":"dateOfBirth",
    "val":"dateOfBirth",
    "scalarType": "date"
    ...

... and this data...

... this table describes how each query behaves:

select firstName, lastName
  from employee

select firstName, middleInitial
  from employee

select firstName, dateOfBirth
  from employee

select *
  from employee

If these columns are purely for data projection, then configuring nullable:true for these columns would help bypass these rules. See Default and nullable columns to understand how default applies. However, using virtual columns configured with a default as part of your search constraint means that this column cannot be used to identify candidate documents to speed up the overall search.

Mixed-Views and Non-Conforming Documents

As described in Comparison of materialized, virtual, and mixed views, virtual columns are immediately available upon configuration, and their values are extracted at query time. But materialized columns are available either only after reindex completes (by default), or immediately if configured with reindexing set to visible. So, a non-conforming document that was included as part of the result set before reindexing completes may be excluded after.

Given a column configuration like this...

"columns":[
  {
    "val":"alliance",
    "scalarType":"string",
    "nullable": false,
    "reindexing": "hidden",
    ...
  }, {
    "val":"weapons",
    "scalarType":"string",
    "virtual": true,
    ...
  }

... and this document...

{
  "character": {
    "name": "hermit",
    "alliance": null,
    "weapon": "staff",
    ...
  }
}

... this query...

op.fromView('acme', 'characters')
  .result()

... includes the previous document as part of the result set upon TDE configuration. However, this same query excludes this result when reindex completes.

Defining View Scope

The scope of a TDE view is constrained by collections, directories, and the parent context of the row.

For details on configuring the scope of a TDE view, see Collections, Directories and Context in the "Template Driven Extraction (TDE)" section in Develop Server-Side Applications.

Security on Views

Template views inherit the permissions configured on the template document. When configured by a non-admin user, one of these roles is required to access the template view:

• tde-admin
• tde-view
• Other roles with read capability configured for the template document itself.

Users without any of these roles will get an error of SQL-TABLENOTFOUND.

A view can be defined using multiple templates, see Creating Views from Multiple Templates. Each template can then be configured with different permissions. For example, template1.json could be configured with student + read, while template2.xml could be configured with teacher + read. Users assigned with either the student or teacher role may access the view.

Access to the rows of a view is further restricted by the permissions on the source document. Given that a document has permission set to rest-reader + read, a user must be assigned both the rest-reader and tde-view role to access the row extracted from the document. Users without the rest-reader role will only see an empty view.

Access to columns of a row is further restricted by the permissions configured for that column. If a column is configured with "permissions":["custom"], a user must be assigned all of the custom, rest-reader and tde-view roles to access the column. Users without the custom role, will see a row without the restricted column.

Access to a column's value is further restricted by any configured element level security on the target of the path expression. See Element Level Security in Secure MarkLogic Server. If the target of the path expression is protected using pii-reader, then users without the pii-reader role will see a column with a null value. However, this works only for virtual views. For materialized views, the entire row is skipped. Any filter on columns is converted to the equivalent cts.query(). See Leveraging the Universal Index to Support Search Across Virtual Columns. This also means that a corresponding query roleset is needed. See Query Rolesets in Secure MarkLogic Server. If the query roleset is not configured, then the column value cannot be referenced as part of the constraints. If the path expression for column cost is protected and no query roleset for the path is configured, then queries that use cost as constraint will always return an empty result set.

Example Workspace on View Security

To better highlight the behavior discussed, we can run some code in Query Console. We begin by creating documents in the Documents database:

'use strict';
declareUpdate()
xdmp.documentInsert(
  '/products/flipflops.json',
  {
    'product': {
      'label': 'flip flops',
      'count': 20,
      'cost': 1.99,
      'price': 2.49
    }
  },
  {
    permissions : [ xdmp.permission('rest-reader', 'read') ]
  }
)

We then configure our custom role:

'use strict';
const sec = require('/MarkLogic/security.xqy');

xdmp.invokeFunction(
  function(){
    declareUpdate(); 

    sec.createRole(
      "custom",
      "to be used for column permissions",
      [],
      [],
      []
    )
  },
  {
    "database" : xdmp.securityDatabase()
  }
)

Then create our test users:

'use strict';
const sec = require('/MarkLogic/security.xqy');

xdmp.invokeFunction(
  function(){
    declareUpdate(); 

    sec.createUser(
      "tde-rest-custom-pii-user", 
      "user that is able to see entire view", 
      "password", 
      ["tde-view", "rest-reader", "custom", "pii-reader"], 
      [], 
      []
    )
    
    sec.createUser(
      "tde-rest-custom-user", 
      "user that is able to see entire view", 
      "password", 
      ["tde-view", "rest-reader", "custom"], 
      [], 
      []
    )
    
    sec.createUser(
      "tde-rest-user", 
      "user that is able to see the view but not a column", 
      "password", 
      ["tde-view", "rest-reader"], 
      [], 
      []
    )
    
    sec.createUser(
      "tde-user", 
      "user that is able to see the view but not a row", 
      "password", 
      ["tde-view"], 
      [], 
      []
    )
  },
  {
    "database" : xdmp.securityDatabase()
  }
)

Finally, we configure our template:

'use strict';
declareUpdate();

const tde = require("/MarkLogic/tde.xqy");

let template = xdmp.toJSON({
  "template": {
    "description": "test column access",
    "context": "/product",
    "rows": [
      {
        "schemaName": "acme",
        "viewName": "products",
        "viewVirtual": true,
        "columns": [
          {
            "name": "label",
            "scalarType": "string",
            "val": "label"
          },
          {
            "name": "count",
            "scalarType": "int",
            "val": "count"
          },
          {
            "name": "cost",
            "scalarType": "float",
            "val": "cost",
            "nullable": true
          },
          {
            "name": "price",
            "scalarType": "float",
            "val": "price",
            "nullable": true,
            "permissions": ["custom"]
          }
        ]
      }
    ]
  }
})
tde.templateInsert("products.json", template, xdmp.permission('tde-view', 'read'))

We can then use this query to test access to our view:

'use strict';

xdmp.invokeFunction(
  function(){
    const op = require('/MarkLogic/optic');

    return op.fromView('acme', 'products')
       .result();
  }, {
    "userId": xdmp.user("tde-user")
  }
)

This table describes the behavior for each user:

We can modify our setup to configure a protected path on cost.

'use strict';
const sec = require('/MarkLogic/security.xqy');

xdmp.invokeFunction(
  function(){
    declareUpdate(); 
    sec.protectPath("cost", null, xdmp.permission("pii-reader", "read", "element"))
  },
  {
    "database" : xdmp.securityDatabase()
  }
)

Re-running our query to access the view results in an empty result set, regardless of user.

We can then modify our view to become virtual:

"schemaName": "acme",
"viewName": "products",
"viewVirtual": true,

This table describes the behavior for each user:

We modify our query to highlight the importance of query rolesets:

'use strict';

xdmp.invokeFunction(
  function(){
    const op = require('/MarkLogic/optic');

    return op.fromView('acme', 'products')
       .where(op.ge(op.col('cost'), 1))
       .result();
  }, {
    "userId": xdmp.user("tde-rest-custom-pii-user")
  }
)

This new query results in an empty result set -- even for a user assigned the pii-reader role.

The query roleset can be configured using this query:

'use strict';
const sec = require('/MarkLogic/security.xqy');

xdmp.invokeFunction(
  function(){
    declareUpdate(); 
    sec.addQueryRolesets(sec.queryRolesets(sec.queryRoleset("pii-reader")))
  },
  {
    "database" : xdmp.securityDatabase()
  }
)

After this change, only the tde-rest-custom-pii-user user is able to search using the cost field. The rest of the users always have an empty result if the column cost is used as filter.

Creating Views from Multiple Templates

You can create a single view from multiple templates. These queries create two documents meant for a single view, Publications:

XQuery

let $med :=
<Citation Status="Completed">
  <ID>69152893</ID>
  <PMID>5717905</PMID>
  <Article>
    <Journal>
      <ISSN>0043-5341</ISSN>
      <JournalIssue>
        <Volume>118</Volume>
        <Issue>49</Issue>
        <PubDate>
          <Year>1968</Year>
          <Month>12</Month>
          <Day>7</Day>
        </PubDate>
      </JournalIssue>
    </Journal>
    <ArticleTitle>
       The Influence of Calcium on Cholesterol in Human Serum
    </ArticleTitle>
    <AuthorList>
      <Author>
        <LastName>Doe</LastName>
        <ForeName>John</ForeName>
      </Author>
      <Author>
        <LastName>Smith</LastName>
        <ForeName>Jane</ForeName>
      </Author>
    </AuthorList>
  </Article>
</Citation>

return xdmp:document-insert("med1.xml", $med)

Javascript

declareUpdate();

xdmp.documentInsert(
  "med2.json",
  {
    "Journal": {
      "Issue": 103,
      "Title": "Bone Density Studies",
      "Date": "8/1/2009",
      "Author": "John Simson" 
    }
  }
)

Those two documents require two different templates to extract rows for the same view. Each template needs different context values, along with a different value for val for each column. For example, the Title column would be populated with the value of the <ArticleTitle> element in the med1.xml file and the Title property in the med2.json file.

This query creates a TDE template to handle the XML document. The context and column's val follows the structure of the XML document:

xquery version "1.0-ml";
import module namespace tde = "http://marklogic.com/xdmp/tde" 
        at "/MarkLogic/tde.xqy";

let $ClinicalView :=
<template xmlns="http://marklogic.com/xdmp/tde">
  <description>populates patients' data</description>
  <context>/Citation/Article</context>
  <rows>
    <row>
      <schema-name>Medical</schema-name>
      <view-name>Publications</view-name>
      <view-layout>sparse</view-layout>
      <columns>
        <column>
          <name>ISSN</name>
          <scalar-type>string</scalar-type>
          <val>Journal/ISSN</val>
          <nullable>true</nullable>
        </column>
        <column>
          <name>Title</name>
          <scalar-type>string</scalar-type>
          <val>ArticleTitle</val>
        </column>
        <column>
          <name>Volume</name>
          <scalar-type>string</scalar-type>
          <val>Journal/JournalIssue/Volume</val>
        </column>
        <column>
          <name>Date</name>
          <scalar-type>string</scalar-type>
          <val>Journal/JournalIssue/PubDate/Month||'/'
             ||Journal/JournalIssue/PubDate/Day||'/'
             ||Journal/JournalIssue/PubDate/Year</val>          
          <nullable>true</nullable>
        </column>
      </columns>
    </row>
  </rows>
</template>
return tde:template-insert("Template.xml", $ClinicalView)

This query creates a TDE template to handle the JSON document. The context and column's val follow the structure of the JSON document:

declareUpdate();

var tde = require("/MarkLogic/tde.xqy");

var ClinicalView = xdmp.toJSON({
  "template": {
    "description": "populates patients' data",
    "context": "/Journal",
    "rows": [
      {
        "schemaName": "Medical",
        "viewName": "Publications",
        "viewLayout": "sparse",
        "columns": [
          [
            {
              "name": "Volume",
              "scalarType": "string",
              "val": "Issue"
            },
            {
              "name": "Title",
              "scalarType": "string",
              "val": "Title"
            },
            {
              "name": "Date",
              "scalarType": "string",
              "val": "Date",
              "nullable": true
            },
            {
              "name": "Author",
              "scalarType": "string",
              "val": "Author",
              "nullable": true
            }
          ]
        ]
      }
    ]
  }
});

tde.templateInsert("Template2.json", ClinicalView);

Note these configurations for both templates:

1. view-layout / viewLayout is set to sparse for both of them. See viewLayout
2. Columns of the same name have the same scalarType, nullable, and so on in both templates. But they differ in value for val.
3. Columns that are not present in the other template are configured with nullable set to true.

The view can then be accessed with this SQL statement:

select * FROM Medical.Publications

The results should look like this:

Availability of Columns During a Database Reindex Operation

This section is applicable only to materialized columns. Virtual columns are always available and are not restricted by any reindex operation.

A column’s accessibility can be considered either offline or online. Accessibility is defined as being able to use that column as part of a query, join, or sort and being part of the row definition.

Columns with reindexing set to hidden are considered offline until reindexing completes. These events can make a column offline:

• Any change to a column’s configuration; for example, changing the configuration of val, collation, dimension, and so on.

• Any change to the enclosing or parent template’s context, directories, collections, var or TDE template permissions.

Columns with reindexing set to visible remain online despite the previous events. But these events make the column offline, regardless of reindexing configuration:

• column is removed from the row configuration.

• row configuration is removed from the TDE template.

• Enclosing or parent template is disabled or deleted.

If all columns of a row are offline, then the entire view becomes offline. Any attempt to access the view is expected to fail with a SQL-TABLEREINDEXING error until reindexing completes. If any column is online, then the view is accessible. However, only the online columns are accessible. Any explicit reference to offline columns results in a SQL-NOCOLUMN error.

Any change in TDE template configuration causing a column to become offline triggers a reindex.

Deleting a template does not trigger a reindex. See Deleting Templates in the "Template Driven Extraction (TDE)" section of Develop Server-Side Applications.

Rules regarding nullable still apply. See Views and Non-Conforming Documents for more information.

Leveraging the Universal Index to Support Search Across Virtual Columns

As described in Comparison of materialized, virtual, and mixed views, data for virtual columns is never persisted to disk. For virtual columns, the search for matching documents can be divided into three steps:

1. Identifying candidate documents.

2. Filtering the candidates.

3. Extracting data.

Identifying candidate documents

The Optic API engine uses various template configurations and query constraints to reduce the number of documents to be filtered. This strategy helps reduce storage I/O, speeding the search.

TDE Template

The value(s) for directories reference(s) the URI lexicon to include only documents whose URI begins with one of the configured value(s).

The value(s) for collections reference(s) the collection lexicon to include only documents with given collection(s).

The value for context uses the universal index to find XML elements or JSON properties that are part of the given path expression.

The value for val uses the universal index to find XML elements or JSON properties that are part of the given path expression. If any of these conditions is true, then this column cannot be used to identify candidates:

• the path expression includes a function like /data().
• the column is configured with "nullable": true.
• the column is configured with a value for default.

Query Constraints

These constraints in a query along with the value that they are compared to can also be used to reduce the number of candidates to be filtered:

op.fromView("acme", "products")
  .where(op.eq(op.col("brand"), 'acme'))

String comparisons are able to identify XML and JSON documents that contain the given element or property name along with the expected value using the universal index:

op.fromView("acme", "products")
  .where(op.eq(op.col("active"), true))

Non-string comparisons are able to identify only JSON documents using the universal index:

op.fromView("acme", "products")
  .where(op.ge(op.col("price"), 9)) 

Values of XML elements are treated as strings.

To identify XML documents with the given element name and non-string value, a range index for that element is required.

Range comparisons require range indexes to be able to identify JSON and XML documents as candidates.

Configure appropriate range indexes to minimize the number of candidates.

Monitoring Query Efficiency

Data for virtual views is never persisted, and the constraints need to be evaluated for each query execution. As discussed in Identifying candidate documents, filter out false positives as much as possible. To observe the effects of your template configuration and constraints in your query, you need to enable a specific trace event: Optic Virtual TDE Debug Fragment Count. You can then see log entries in {PORT}_ErrorLog.txt like these:

2025-07-11 15:09:30.847 Info: [Event:id=Optic Virtual TDE Debug Fragment Count] [
  {
    "databaseFragmentCount": 8,
    "sessionKey": "371307799289567188",
    "type": "generalInfo",
    "viewIRI": "http://marklogic.com/view/acme/base"
  },
  {
    "constrainingQueryCount": 1,
    "fragmentsInScope": 6,
    "fragmentsInScopeChange": -2,
    "queryFound": true,
    "queryHash": "6013961757958342495",
    "type": "contextQuery"
  },
...

• This log snippet was formatted for easier reading.

• "type": "generalInfo" provides a base count for the number of documents (fragments) in your database.

• Other entries provide an estimate of how much this base count is reduced when applying a particular filter.

• This template’s context reduced the number of documents to be filtered by 2. Use this trace event to check how effective your configured range indexes are with regards to your virtual views.

Filtering

The list of candidates includes false positives. The filtering step compares actual values on the provided context and constraints to remove false positives from the list of matching documents. Continuing with our previous scenario, given this query ...

op.fromView("acme", "products")
  .where(op.ge(op.col("price"), 9))

... the matching document is read, and the corresponding value for the column price is compared against the provided constraint (9). A similar process is applied for all constraints as part of the entire pipeline or SQL statement.

This step is performed before any sorting or pagination is involved.

Documents that contribute multiple rows can be severely affected by this step. Given this template ...

{
  "template":{
  "description":"a view on book sequences",
  "context":"/book/sections/sequence",
  "rows":[ {
    "schemaName":"acme",
    "viewName":"sequences",
    "viewVirtual": true,
    "columns":[
      {
        "name":"section",
        "scalarType":"long",
        ...
      }, {
        "name":"sequence",
        "scalarType":"long",
        ...
      }

... and this data ...

{
  "book": {
    "sections": [{
      "section": 1,
      "sequence": [{
        "sequence": 1,
        ...
      }, {
        "sequence": 2,
        ...
      }]
    }, {
      "section": 2,
      "sequence": [{
        "sequence": 3,
        ...
      }, {
        "sequence": 4,
        ...
      }]
    }]
  }
}

... this query ...

SELECT * 
FROM acme.sequences
WHERE section = 1 
and sequence = 4

... needs to open and read the document above, only to return an empty result set.

Data Extraction

Building the actual row by extracting referenced data is the last step. It happens before any sorting or pagination on virtual columns.

Enforcing the rules on non-conforming documents happens only at this point. See Virtual Views and Non-Conforming Documents and Mixed-Views and Non-Conforming Documents.

Concerns and Considerations

Element Level Security

Virtual views on documents with Element Level Security in scope perform slower than documents without Element Level Security in scope.

Switching between materialized and virtual views

Views and columns can be toggled between materialized and virtual:

• Switching from virtual to materialized: Reindexing is required before the toggled view or column becomes available.
• Switching from materialized to virtual:
  • TDE created in MarkLogic 12: Reindexing is not required.
  • TDE created prior to MarkLogic 12: Reindexing is required only the first time it is switched.

Effects of Document Fragmentation

By default, an entire document is considered a single fragment. Though not usually recommended, this default can be changed by adjusting the fragmentation strategy for a particular database. See Fragments in Administrate MarkLogic Server.

Materialized views/columns do not support fragmented documents as the TDE templates only have one fragment in scope while processing. Depending on the way the template is written, the rows index will not be populated correctly for fragmented documents. However, because virtual views/columns fetch complete documents when extracting values, they do behave as expected with fragmented documents. Though virtual views/columns do work with fragmented documents, document fragmentation is not recommended in most circumstances due to its complex behavior and interaction with other features in MarkLogic.