Search Documents from two collections in MarkLogic

Question

In Marklogic, I want to search between two collections by joining the id element of doc from collection1 to id element of doc from collection2. When it is matched i need the resulting document from both collections. I have the below code, but it is very slow. How to use cts:search or search:search to achieve the same

for $i in collection('demographic')/individual,
    $j in collection('membership')/membership[enrolleIndividualId/id/text() = $i/individual/id/text()])  
return {$i,$j}

joemfb joemfb · Accepted Answer · 2015-09-22T05:31:07

Update:

I should note that your sample is not valid XQuery: return element root { $i, $j } would be valid. Also, you should not use the /text() node selector, as it's behavior can be counterintuitive. You can compare elements directly in an XPath predicate ([enrolleIndividualId/id eq $i/individual/id]). Use /fn:string() in place of /text() if you need the contents of an element as a string. I'd also recommend using the atomic equality operator eq in place of the sequence equality operator = when directly comparing individual elements.

Original Answer:

There are several approaches to implementing joins in MarkLogic, but I would first question your data model. From the names of the elements in your sample query, it looks like you are using a relational model (individuals have memberships). MarkLogic is a document database, and it's optimized for denormalized documents. You will be much better served to process your data and generate new individual documents that each contain the relevant membership data.

That being said, here's how you could join your documents:

First, you will need range indices to write performant joins. If the id element from your sample query is not unique to individuals, you will need path range indices on enrolledIndividualId/id and individual/id, otherwise, a simple element range index on id will do.

The most common join pattern in MarkLogic uses a "shotgun-OR" query; first retrieving values from the lexicon backing a range index, and then constructing an or-query from those values to retrieve the relevant documents. This won't work directly in your case, as you want to retrieve both sides of the join. You can either run a search for each pair of documents, or run a single search for one side, and then an additional document read for each document.

pairs:

for $value in cts:values(cts:path-reference("individual/id"))
return
  cts:search(/,
    cts:or-query((
      cts:and-query((
        cts:collection-query("demographic"),
        cts:path-range-query("individual/id", "=", $value))),
      cts:and-query((
        cts:collection-query("membership"),
        cts:path-range-query("enrolledIndividualId/id", "=", $value))))),
    "unfiltered")

shotgun-OR plus iteration:

for $doc in 
  cts:search(/,
    cts:and-query((
      cts:collection-query("demographic"),
      cts:path-range-query("individual/id", "=",
        cts:values(cts:path-reference("individual/id"))))),
    "unfiltered")
return
  cts:search(/,
    cts:and-query((
      cts:collection-query("membership"),
      cts:path-range-query("enrolledIndividualId/id", "=", $doc/individual/id))),
    "unfiltered")

As you can see, each approach requires I/O proportionate to the number of docs/values you want to join. If you only needed the shotgun-OR (ie, a query for documents based on criteria from other documents), you would only need to make two requests, the initial cts:values() call to retrieve values from a lexicon, and the cts:search() call using a query built from those values.

Note: the cts:query objects used in these examples could be used in conjunction with the Search API by means of the search:resolve() function.

Given your apparent data model, you will be much better served by processing your data into individual, de-normalized documents.

Search Documents from two collections in MarkLogic

1 Answers