Search code examples
marklogicmarklogic-9cts-search

Search with element-range-query on date field if some of the documents have empty-value elements

I have a element-range-index created on the modificationDateTime element.

Our xml is something like this:

<data>
  <id>456789</id>
  <modificationDateTime>2018-10-15T15:16:37.047Z</modificationDateTime>
  <otherElementsFollow> ... </otherElementsFollow>
</data>

I am using the following query to get the documents for 15th Oct 2018 or even a range 15th to 16th:

cts:search(/, (
    cts:and-query((
       cts:directory-query("/my-directory/", "1")
       , cts:element-range-query(xs:QName("modificationDateTime"), ">=", xs:dateTime("2018-10-15T00:00:00.000+05:30"))
       , cts:element-range-query(xs:QName("modificationDateTime"), "<=", xs:dateTime("2018-10-15T23:59:59.000+05:30"))
    ))
)

I get the following error when i run the query:

[1.0-ml] XDMP-CAST: (err:FORG0001) cts:search(fn:collection(), cts:and-query((cts:directory-query("/my-directory/", "1"), cts:element-range-query(xs:QName("modificationDateTime"), ">=", xs:dateTime("2018-10-15T00:00:00+05:30"), (), 1), cts:element-range-query(xs:QName("modificationDateTime"), "<=", xs:dateTime("2018-10-15T23:59:59+05:30"), (), 1)), ())) -- Invalid cast: "" cast as xs:dateTime

After some analysis i found that there are some XMLs which have <modificationDateTime> element value as empty like:

<data>
  <id>1234567</id>
  <modificationDateTime></modificationDateTime>
  <otherElementsFollow> ... </otherElementsFollow>
</data>

So this is what is causing this issue and we can't remove this element neither we can't have some value since empty is a valid value.

Any help as to how to search with range index on empty elements, ideally it should consider these records but when i update the empty values to a valid dateTime value the same query works properly.

Solution

  • If excluding the documents with those empty elements is an option, you could append this not-query to your and-query to do so:

    cts:not-query(
  cts:element-value-query(xs:QName("modificationDateTime"), "")
)

    HTH!