CODEWITHSUNDEEP
oracle-databasemarklogic
thichxai
thichxai

Reputation: 1133

Marklogic how to construct a cts query

I have a Oracle query and would like to transform into Marklogic cts query. It looks like Marklogic CTS doesn't allow to have "and-query" inside of "and-query". I am not sure how Marklogic works. Thanks in advance.

Where clause query:

where (collection = "TRBA" AND fulltext = 1 
       AND (dnta = "Briefing" OR dnta = "Conference" OR snta = "Workshop"
            OR snta = "Published in" AND (snta = "this article" OR dnta = "Journal")
      )
      AND (cand IN ("Research","Development Center") OR scn IN("424778","98814","393825"))

Translate into Marklogic:

let $uris:= cts:uris(
                     (),
                     (),
                     cts:and-query((
                          cts:collection-query("/dbs/"TRBA"),
                          cts:element-value-query(xs:QName("meta:FullTextExists"),"1"),
                          cts:field-word-query("dnta",("briefing","conference")),

                         cts:or-query((
                              cts:element-word-query(xs:QName("meta:snta"),("this article")),
                              cts:field-word-query("dnta",("Journal")),

                        cts:and-query((
                             cts:or-query((
                                   cts:field-word-query("cand", ("Research","Development Center"))
                                   cts:field-word-query("scn",("424778","98814","393825")) 
                             ))
                          ))(:inside and-query:)
                        ))(:or-query:)
                     ))(:outside and-query:)

 return fn:doc($uris)

Upvotes: 1

Views: 239

Answers (2)

mholstege
mholstege

Reputation: 4912

  1. There are basic syntax errors in your code above: missing parens, extra double quotes
  2. I don't think you want word query as the translation for "="; word query just says that word appears somewhere in the field in question; I would think that would be a value query instead.
  3. You might want to take a look at cts:parse which takes a string with ANDs and ORs etc. plus bindings for fields and parses a query string into a cts:query

That said, if you assume the AND mixed in with the ORs binds to the closest clause, i.e. as parenthesized so:

(collection = "TRBA" AND
 fulltext = 1  AND
 (dnta = "Briefing" OR
  dnta = "Conference" OR
  snta = "Workshop" OR
  (snta = "Published in" AND (snta = "this article" OR dnta = "Journal"))
 ) AND
 (cand IN ("Research","Development Center") OR
  scn IN ("424778","98814","393825"))

then I would translate this something like this:

cts:and-query((
  cts:collection-query("/dbs/TRBA"),
  cts:element-value-query(xs:QName("meta:FullTextExists"),"1"),
  cts:or-query((
    cts:field-value-query("dnta",("Briefing","Conference")),
    cts:field-value-query("snta","Workshop"),
    cts:and-query((
      cts:field-value-query("snta","Published in"),
      cts:or-query((
        cts:field-value-query("snta","this article"),
        cts:field-value-query("dnta","Journal")
      ))
    ))
  )),
  cts:or-query((
    cts:field-value-query("cand",("Research","Development Center")),
    cts:field-value-query("scn",("424778","98814","392825"))
  ))
))

It is a pretty direct mapping.

Upvotes: 4

hunterhacker
hunterhacker

Reputation: 7122

You simply have several typos in your code. There's an extra double quote in the collection-query and you're missing a comma between items in the last or-query.

Once fixing those the code will run. But a pro tip: don't ever fetch URIs only to fetch documents. You're wasting effort. Just fetch the documents directly with a search passing the query.

let $q := cts:and-query((...))
return cts:search(doc(), $q)[1 to 10]

You probably want to add a limit like [1 to 10] as well unless you really intend to return the full result set.

Upvotes: 2

Related Questions