1

I am new to marklogic. I am trying to execute simple less than/ greater than query using marklogic nosql database in Java application.

Java: v14 Marklogic: v9

Lets say i have a "user" database and sample document looks like below:

{
 "name": "some name",
 "dateOfBirth": "1991-07-01",
 ...
 ...
}

SQL version of my expected query is select * from user where dateOfBirth > "1980-01-01"

Used below code in Java

StructuredQueryBuilder qb = new StructuredQueryBuilder();

StructuredQueryDefinition structuredQueryDefinition = qb.range(qb.pathIndex("/dateOfBirth"),
        "xs:string",
        (String[]) null,
        StructuredQueryBuilder.Operator.GT,
        eachCriteria.getValue());

markLogicTemplate.search(CombinedQueryDefinitionBuilder.combine(structuredQueryDefinition), User.class);

Created path index using below code:

xquery version "1.0-ml";

  import module namespace admin = "http://marklogic.com/xdmp/admin"
      at "/MarkLogic/admin.xqy";

  let $config := admin:get-configuration()
  let $dbid := xdmp:database("user")
  let $pathspec := admin:database-range-path-index(
  $dbid,
  "string",
  "/dateOfBirth",
  "http://marklogic.com/collation/",
  fn:false(),
  "ignore")
  return
    admin:database-add-range-path-index($config, $dbid, $pathspec)

Getting below exception in Java:

com.marklogic.client.FailedRequestException: Local message: search failed: Bad Request. Server Message: XDMP-PATHRIDXNOTFOUND: cts:search(fn:collection(), cts:and-query((cts:collection-query("User"), cts:path-range-query("/dateOfBirth", ">", "1980-01-01", ("collation=http://marklogic.com/collation/"), 1)), ()), ("unfiltered", cts:score-order("descending")), xs:double("0"), ()) -- No string path range index for /dateOfBirth collation=http://marklogic.com/collation/

Tried re-running marklogic server after creating index but still no luck.

Thanks in advance for help.

Aviv Yaniv
  • 6,188
  • 3
  • 7
  • 22
Akshay
  • 11
  • 1

2 Answers2

0

To use a range index in a query, the range index and query must specify

  • the same data type
  • the same collation for a string data type

I'm guessing that the data type should be date for this range index and query (so a greater date matches even if the string is lesser). String values in JSON documents can be indexed as date values (and as many other data types).

Depending on your requirements, you might consider indexing the documents with TDE and projecting rows out of the documents. In the Java API, you can then use RowManager to retrieve the rows. See:

Hoping that helps,

ehennum
  • 7,295
  • 13
  • 9
0

There are fundamental issues with the aforesaid range index and Java implementation:

The "/dateOfBirth" is W3C non-compliant XML/XPath. XML document must have a root element. Java API will not return any result with invalid XPath.

My sample documents:

/person1.xml

<person>
    <name>Alice Alice</name>
    <dob>1991-07-01</dob>
</person>

/person2.xml

<person>
    <name>Lewis Carroll</name>
    <dob>1981-07-01</dob>
</person>

You can create either date or string type of range index depending on the application needs. The Java query notations must conform to MarkLogic Java API standard.

Solution one: I create a string path range index -> /person/dob

Java code:

StructuredQueryDefinition queryDef = sqb.range(sqb.pathIndex("/person/dob"), "xs:string", Operator.GT, "1980-01-01");

Java Logging:

Query result: 

<search:response snippet-format="snippet" total="2" start="1" page-length="10" xmlns:search="http://marklogic.com/appservices/search">
  <search:result index="1" uri="/person1.xml" path="fn:doc(&quot;/person1.xml&quot;)" score="0" confidence="0" fitness="0" href="/v1/documents?uri=%2Fperson1.xml" mimetype="application/xml" format="xml">
    <search:snippet>
      <search:match path="fn:doc(&quot;/person1.xml&quot;)/person/dob"><search:highlight>1991-07-01</search:highlight></search:match>
    </search:snippet>
  </search:result>
  <search:result index="2" uri="/person2.xml" path="fn:doc(&quot;/person2.xml&quot;)" score="0" confidence="0" fitness="0" href="/v1/documents?uri=%2Fperson2.xml" mimetype="application/xml" format="xml">
    <search:snippet>
      <search:match path="fn:doc(&quot;/person2.xml&quot;)/person/dob"><search:highlight>1981-07-01</search:highlight></search:match>
    </search:snippet>
  </search:result>
...............
</search:response>

Session completed: 2020-09-07T15:49:26.409488

Solution two: I create a date path range index -> /person/dob

Java API produces the same result in this scenario.

Java code:

StructuredQueryDefinition queryDef = sqb.range(sqb.pathIndex("/person/dob"), "xs:date", Operator.GT, "1980-01-01");

You can always use other query constructs to achieve the desired results.

Fiona Chen
  • 1,358
  • 5
  • 16