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)
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.