Trying to sort MarkLogic collection query result on a dateTime index

Question

I am using node.js to setup an application. I am using node to test some stuff. I use the marklogic module in node.js to query the database and return JSON. For this I wrote a transform on the XML content, that works.

Now I want to order the result of a collection query on the timestamp in the data. The timestamp has an index on it of type xs:dateTime. And lives in a namespace "http://exmaple.com/sccs".

Example document:

<?xml version="1.0"?>
<obj:object xmlns:obj="http://marklogic.com/solutions/obi/object">
  <obi:metadata xmlns:obi="http://marklogic.com/solutions/obi" createdBy="user-app-user" createdDateTime="2015-10-26T16:42:30.302458Z" lastUpdatedBy="user-app-user" lastUpdatedDateTime="2015-10-26T16:45:01.621435Z">
</obi:metadata>
  <obj:label>This alert was send based on the First Time Seen RuleThis subject was spotted first time at this sensor location</obj:label>
  <obj:type>alert</obj:type>
  <obj:id>c2151ee0-f0a9-4eb5-85c2-1c5b3c7c7a65</obj:id>
  <obj:content>
    <alert xmlns="http://example.com/sccs/alert">
      <obj:property sourceCount="1">
        <actions type="array" elementType="string">
          <action>This alert was send based on the First Time Seen Rule</action>
          <action>This subject was spotted first time at this sensor location</action>
        </actions>
      </obj:property>
      <obj:property sourceCount="1">
        <status>Inactive</status>
      </obj:property>
      <obj:property sourceCount="1">
        <sensor-id>test-sensor-id</sensor-id>
      </obj:property>
      <obj:property sourceCount="1">
        <device-id>00:00:00:00:00:04</device-id>
      </obj:property>
      <obj:property sourceCount="1">
        <alertType>trespasser-alert</alertType>
      </obj:property>
      <obj:property sourceCount="1">
        <position>{"type":"Point", "coordinates":[52.2, 4.3]}</position>
      </obj:property>
      <obj:property sourceCount="1">
        <scc:id xmlns:scc="http://example.com/sccs">04fdef0a-9d3f-4743-9e88-04da279a0c37</scc:id>
      </obj:property>
      <obj:property sourceCount="1">
        <scc:timestamp xmlns:scc="http://example.com/sccs">2015-10-01T13:06:00Z</scc:timestamp>
      </obj:property>
    </alert>
  </obj:content>
  <obj:workspaces>
    <obj:workspace id="Public">
</obj:workspace>
  </obj:workspaces>
  <obj:sourceIds count="1">
    <source:id xmlns:source="http://marklogic.com/solutions/obi/source">57358890-8d71-4515-90c1-5cacc54347f7</source:id>
  </obj:sourceIds>
</obj:object>

Now my node script:

var marklogic = require('marklogic');
var my = require('./my-connection.js');

var db = marklogic.createDatabaseClient(my.connInfo);
var qb = marklogic.queryBuilder;

var options = {
    "sort-order": [
      {
        "direction": "descending",
        "type": "xs:dateTime",
        "collation": "http://marklogic.com/collation/codepoint",
        "element": {
          "name": "timestamp",
          "ns": "http://sensingclues.com/sccs",
        },
        "annotation": [ "some user comment can go here" ] },
      {
        "direction": "ascending",
        "score": null
      }
    ]
  };

db.documents.query(
  qb.where(
    qb.collection("alert")
    ).orderBy(qb.sort('timestamp'))//.withOptions(options)//.orderBy(qb.sort('timestamp'))
    //.slice(qb.transform('alerts-query-transform')) // HK :use transform
).result( function(documents) {
    var arrAlerts = new Array();

    console.log('The alerts collection:')

    documents.forEach( function(document) {

      arrAlerts.push(document.content);

    });
    console.log(arrAlerts);

}, function(error) {
    console.log(JSON.stringify(error, null, 2));
});

Gives:

node alerts-no-transform-test.js 
{
  "message": "query documents: response with invalid 400 status",
  "statusCode": 400,
  "body": {
    "errorResponse": {
      "statusCode": 400,
      "status": "Bad Request",
      "messageCode": "SEARCH-BADORDERBY",
      "message": "SEARCH-BADORDERBY: (err:FOER0000) Indexes are required to support element, element-attribute, json-property, or field sort specifications."
    }
  }
}

If I try to use options as defined also above I get:

node alerts-no-transform-test.js 
/home/hugo/git/sccss-middletier/cluey-app/node_modules/marklogic/lib/query-builder.js:4807
        throw new Error('unknown option '+key);
              ^
Error: unknown option sort-order
    at QueryBuilder.withOptions (/home/hugo/git/sccss-middletier/cluey-app/node_modules/marklogic/lib/query-builder.js:4807:15)
    at Object.<anonymous> (/home/hugo/git/sccss-middletier/cluey-app/alerts-no-transform-test.js:33:6)
    at Module._compile (module.js:460:26)
    at Object.Module._extensions..js (module.js:478:10)
    at Module.load (module.js:355:32)
    at Function.Module._load (module.js:310:12)
    at Function.Module.runMain (module.js:501:10)
    at startup (node.js:129:16)
    at node.js:814:3

Question : What is the correct way to sort the result based on a timestamp dateTime index?

Answer 1

ok I found a pointer on SO here

Now this works:

// get all the devices from ML
db.documents.query(
  qb.where(
    qb.collection("alert")
    ).orderBy(qb.sort(qb.element(qb.qname('http://example.com/sccs', 'timestamp')),
  'descending'))
    .slice(qb.transform('alerts-query-transform')) // HK: use transform
).result( function(documents) {

Apparently I have to explicitly point to the element? Is there any useful documentation on how to use the query builder in detail?

On the side : note the transform applied to the XML to map to JSON ...

Answer 2

You mention this collation in the sample sort order configuration:

"collation": "http://marklogic.com/collation/codepoint",

However, you mention no specific collation in your index configuration. Yet in MarkLogic 8 the default collation is not codepoint but the UCA Root Collation

It is possible that you are simply trying to sort on a non-existent index (because the index is created with the default collation and your code is using the codepoint collation).

I suspect this because of the message:

  "message": "SEARCH-BADORDERBY: (err:FOER0000) Indexes are required to support element, element-attribute, json-property, or field sort specifications."

In situations like this, I always use cts:element-values() or cts:values() etc.. in queryConsole to test my index and make sure it is exactly as I expect - before I try to refer to it in code. This may help you ensure that the index is what you expect.