MarkLogic Join Query

Question

Hi I am new to marklogic and in Xquery world. I am not able to think of starting point to write the following logic in Marklogic Xquery. I would be thankful if somebody can give me idea/sample so I can achieve the following:

I want to Query A.XML based on a word lookup in B.XML. Query should produce C.XML. The logic should be as follows:

A.XML

<root>
<content> The state passed its first ban on using a handheld cellphone while driving in 2004 Nokia Vodafone Nokia Growth Recession Creicket HBO</content>
</root>

B.XML

<WordLookUp>
<companies>
    <company name="Vodafone">Vodafone</company>
    <company name="Nokia">Nokia</company>
</companies>
<topics>
    <topic group="Sports">Cricket</topic>
    <topic group="Entertainment">HBO</topic>
    <topic group="Finance">GDP</topic>
</topics>
<moods>
    <mood number="4">Growth</mood>
    <mood number="-5">Depression</mood>
    <mood number="-3">Recession</mood>
</moods>

C.XML (Result XML)

<root>
    <content> The state passed its first ban on using a handheld cellphone while driving in 2004 Nokia Vodafone Nokia Growth Recession Creicket HBO</content>
    <updatedElement>
        <companies>
            <company count="1">Vodafone</company>
            <company count="2">Nokia</company>
        </companies>
        <mood>1</mood>
        <topics>
             <topic count="1">Sports</topic>
             <topic count="1">Entertainment</topic>
        </topics>
            <word-count>22</word-count>
    </updatedElement>
    </root>

Search each company/text() of A.xml in B.xml, if match found create tag: TAG {company count="Number of occurrence of that word"}company/@name {/company}
Search each topic/text() of A.xml in B.xml, if match found create tag TAG {topic topic="Number of occurrences of that word"}topic/@group{/topic}
Search each mood/text() of A.xml in B.xml, if match found [occurrences of first word * {/mood[first word]/@number}] + [occurrences of second word * {/mood[second word]/@number})]....
get the word count of element.

You are counting "2004" as a word. Could you, please, edit the question and define "word" exactly? Would thw words in <content> be always space-separated or could there be other delimiters? Is "word-count" one word or two? What about FY-2012? — Dimitre Novatchev
It might be interesting to know that a fully-compliant XQuery solution exists that doesn't use any extension functions, and thus can be run under any compliant XQuery implementation. — Dimitre Novatchev

Evan Lenz Evan Lenz · Accepted Answer · 2012-04-15T03:50:24

This was a fun one, and I learned a few things in the process. Thanks!

Note: to get the results you wanted, I fixed a typo in A.xml ("Creicket" -> "Cricket").

The following solution uses two MarkLogic-specific functions:

cts:highlight (for replacing matching text with nodes which you can then count)
cts:tokenize (for breaking up a given string into word, space, and punctuation parts)

It also includes some powerful magic specific to those two functions, respectively:

the dynamic binding of the special variable $cts:text (which isn't really necessary for this particular use case, but I digress), and
the data model extension which adds these subtypes of xs:string:
- cts:word,
- cts:space, and
- cts:punctuation.

Enjoy!

xquery version "1.0-ml";

(: Generic function using MarkLogic's ability to find query matches within a single node :)
declare function local:find-matches($content, $search-text) {
  cts:highlight($content, $search-text, <MATCH>{$cts:text}</MATCH>)
  //MATCH
};

(: Generic function using MarkLogic's ability to tokenize text into words, punctuation, and spaces :)
declare function local:get-words($text) {
  cts:tokenize($text)[. instance of cts:word]
};

(: The rest of this is pure XQuery :)
let $content := doc("A.xml")/root/content,
    $lookup  := doc("B.xml")/WordLookUp
return
  <root>
    {$content}
    <updatedElement>

      <companies>{
        for $company in $lookup/companies/company
        let $results := local:find-matches($content, string($company))
        where exists($results)
        return
          <company count="{count($results)}">{string($company/@name)}</company>
      }</companies>

      <mood>{
        sum(
          for $mood in $lookup/moods/mood
          let $results := local:find-matches($content, string($mood))
          return count($results) * $mood/@number
        )
      }</mood>

      <topics>{
        for $topic in $lookup/topics/topic
        let $results := local:find-matches($content, string($topic))
        where exists($results)
        return
          <topic count="{count($results)}">{string($topic/@group)}</topic>
      }</topics>

      <word-count>{
        count(local:get-words($content))
      }</word-count>

    </updatedElement>
  </root>

Let me know if you have any follow-up questions about how all the above works. At first, I was inclined to use cts:search or cts:contains, which are the bread and butter for search in MarkLogic. But I realized that this example wasn't so much about search (finding documents) as it was about looking up matching text within an already-given document. If you needed to extend this somehow to aggregate across a large number of documents, then you'd want to look into the additional use of cts:search or cts:contains.

One final caveat: if you think your content might have <MATCH> elements already, you'll want to use a different element name when calling cts:highlight (a name which you can guarantee won't conflict with your content's existing element names). Otherwise, you'll potentially get the wrong number of results (higher than the accurate count).

ADDENDUM:

I was curious if this could be done without cts:highlight, given that cts:tokenize already breaks up the text into all the words for you. The same result is produced using this alternative implementation of local:find-matches (provided you swap the order of the function declarations because one depends on the other):

(: Find word matches by comparing them one-by-one :)
declare function local:find-matches($content, $search-text) {
  local:get-words($content)[cts:stem(.) = cts:stem($search-text)]
};

It uses cts:stem to normalize the given word to its stem, so, for example searching for "pass" will match "passed", etc. However, this still won't work for multi-word (phrase) searches. So to be safe, I'd stick with using cts:highlight, which, like cts:search and cts:contains, can handle any cts:query you give it (including simple word/phrase searches like we do above).

MarkLogic Join Query

3 Answers