CODEWITHSUNDEEP
xquerymarklogic
Sebastian Stuecker
Sebastian Stuecker

Reputation: 113

XQuery Create where clause based on xml structure as a kind of dynamic where clause

this is about XQuery - I am using MarkLogic as Database.

I have data as in the following example:

<instrument name="myTest1" id="test1">
   <daten>
      <daily>
         <day date="2016-02-05">
            <screener>
               <column name="i1">
                  <value>1</value>
                  <bg>red</bg>
               </column>
               <column name="i2">
                  <value>1</value>
                  <fg>lime</bg>
               </column>
               <column name="i4">
                  <fg>black</bg>
               </column>
            </screener>
         </day>
      </daily>
   </daten>
</instrument>

I have many instruments, and each one has an entry for each day in the daily element, and inside screener, there can be manz columns, all with different names. Some screeners include more columns than others. Each column can include a value element, a bg element and a fg element.

I want to search for instruments that fullfill specific criteria about what kind of columns do have children with specific values. Example: I want a sequence of all instruments, that for a given day, have a value 1 for column i1 and that have a fg black for column i2

Since I have many different of those conditions, I would not like to hardcode them in XQuery where clauses. I did that for a few and it works, but the code gets a lot of duplications and is hard to maintain.

My question is, is it possible to build a where clause in a FLOWR statement programatically, meaning, based on another xml structure, which could look like this:

<searchpatterns>
  <pattern name="test1">
     <c>
             <name>i1</name>
             <element>value</element>
             <value>1</value>
          </c>
          <c>
             <name>i2</name>
             <element>fg</element>
             <value>red</value>
             <modifier>not</modifier>
          </c>
  </pattern>
</searchpatterns>

which would find those instruments, where the screener has a column i1 which itself has a value of 1, and also it must not have column i2 with a fg of red.

When I do it the normal way I query my date like this:

for $res in doc()/instrument
   where $res/daten/daily/day[@date="2016-02-05"]/screener/column[@name="i1"]/value/text()="1"
      and res/daten/daily/day[@date="2016-02-05"]/screener/column[@name="i2"]/fg/text()!="red"

This kind of where clause I want to generate based on an XML structure.

I did some research of the MarkLogic inbuilt cts:search function and a lot of stuff around it but it seems to be for something else (more user interactive searching)

If you have a hint to point me in the right direction, if what I want is even possible, I would very much appreciate it.Thanks!

Upvotes: 3

Views: 420

Answers (2)

ehennum
ehennum

Reputation: 7335

The doc()/instrument XPath asks for every document with an instrument element and then filters those documents.

Where possible, it's usually better in MarkLogic to model the documents so you can use the indexes to retrieve as few documents as possible. It's also usually better to use cts:search() instead of XPath to generate the sequence so you are working directly with the indexes.

In this case, you might consider using the values of the name attribute as elements instead of the generic "column." You could then generate a cts:element-query that matches the name containing a cts:element-value-query that matches the value within the name.

Hoping that helps,

Upvotes: 2

dirkk
dirkk

Reputation: 6218

Yes, this can be achieved programmatically. If you want to check whether an element satisifes a test for every item in a sequence, the every ... satisfies construct comes to mind. So in this case it could be:

for $res in doc()/instrument
where every $pattern in $searchpatterns/pattern/c satisfies (
  let $equal := $res/daten/daily/day[@date="2016-02-05"]/screener/column[@name = $pattern/name]/*[name() = $pattern/element] = $pattern/value
  return if ($pattern/modifier = "not") then not($equal) else $equal
)  
return $res

So every $pattern will be checked. I assume the modifier element is supposed to modify the equal construct. So we first check if the element satisfies the equal condition and the we check whether the modifier element is equal to not. Of course, applying the same idea could also be used to implement other modifiers as well.

Upvotes: 2

Related Questions