MarkLogic Optic API: Transformations in TDE config <val> template

Question

For doing transformations in TDE template view configuration XML, would the following not be possible?

<column>
      <name>myColumn</name>
      <scalar-type>string</scalar-type>
      <val>
        if (count(distinct-values(collection("my_coll")//instance[id eq '31234']/field1)) gt 1)
        then "Multiple Values"
        else "Single Values"
      </val>
      <nullable>true</nullable>
      <invalid-values>ignore</invalid-values>
</column>

https://docs.marklogic.com/10.0/guide/app-dev/TDE#id_99178

Reading the documentation above, it looks like fn:collection() is not supported on this list. Or the XPath is not supported.

Is there an alternative way I could get this done before resorting to pre-template document transformation?

Answer 1

From MarkLogic 10.0-7, you could accomplish this by creating an optic plan that does such logic and saving the plan as a view. This is covered under the documentation for Query Based Views

In that plan, you would be doing a group-by with a count on the items you wanted to be unique and then after that group-by, bind on a new column using op:case()

A handy Example to get you going is below. However, you must always play nicely with your memory and indexes.. in your example, you are using xPath into an instance ID. This will not scale. You likely should start with something indexed already in a lexicon or in a view - even if it is one column. The example is generic - but once you have a view with the thing you care about the "one or many" question, then the pattern should work for you.

    xquery version "1.0-ml";

import module namespace op="http://marklogic.com/optic"
     at "/MarkLogic/optic.xqy";


let $plan := op:from-literals((
map:entry("animal", "llama")=>map:with("name", "Jethro"),
map:entry("animal", "emu")=>map:with("name", "Sven"),
map:entry("animal", "llama")=>map:with("name", "Samantha")
))
=> op:group-by(op:col("animal"), op:count("theCount", op:col("name")))
=> op:bind(op:as("oneOrMany", op:case((op:when(op:gt(op:col("theCount"), 1), "many")),"JustOne")))
=> op:select(("animal", "oneOrMany"), "myTable")

let $view := $plan=>op:generate-view("myFarm", "animals")


return xdmp:invoke-function(function(){
  xdmp:document-insert(
    "view-farm-animals.xml", 
    $view, 
    map:entry("permissions", xdmp:permission("admin", "read", "object"))
      =>map:with("collections", "http://marklogic.com/xdmp/qbv"))
  }, map:entry("database", xdmp:schema-database())
 )

And the result of using the optic-based view: select * from myFarm.animals where oneOrMany='many':

myFarm.animals.animal   myFarm.animals.oneOrMany
llama                   many

(and the winner is .. llamas)