Querybuilder.json: Orderby works only for some properties

[henrikh65523654]

We have som issues sorting results from querybuilder.json (AEM 6.2). We want to be able to sort data on different properties, specified by the user (i.e. path, lastModified, title++).  

Ordering data by "path" and "title" works fine:
querybuilder.json?path=%2Fcontent&type=cq%3APage&p.hits=selective&p.properties=jcr%3Apath%20jcr%3Atitle%20cq%3AlastModified&p.limit=-1&orderby=path&orderby.sort=asc

Ordering data by jcr:content properties does not work (list in random order), i.e. cq:lastModified:
querybuilder.json?path=%2Fcontent&type=cq%3APage&p.hits=selective&p.properties=jcr%3Apath%20jcr%3Atitle%20cq%3AlastModified&p.limit=-1&orderby=@jcr%3Acontent%2Fcq%3AlastModified&orderby.sort=asc

(Same as documented in QueryBuilder-api https://docs.adobe.com/docs/en/aem/6-2/develop/search/querybuilder-api.html#Sample Queries)

Trying different variations on specifying orderby doesnt help:
querybuilder.json?path=%2Fcontent%2Fsites%2Fsb1%2Fnb%2Fsmn%2Fprivat%2Fforsikring&type=cq%3APageContent&p.hits=selective&p.properties=jcr:title%20cq:lastModified&p.limit=10&orderby=jcr:content%2Fcq%3AlastModified&orderby.sort=asc (Without "@", fails)
querybuilder.json?path=%2Fcontent%2Fsites%2Fsb1%2Fnb%2Fsmn%2Fprivat%2Fforsikring&type=cq%3APageContent&p.hits=selective&p.properties=jcr:title%20cq:lastModified&p.limit=10&orderby=cq%3AlastModified&orderby.sort=asc (Without "@jcr:content", fails)

Same issue if we try to order by i.e cq:lastRolledout, jcr:created. 

Anyone experienced same issue? 

Best regards

Henrik Heiestad

[kautuk_sahni]

Hi 

Please refer to this wonderful community article:

Link:- https://hashimkhan.in/2015/12/02/query-builder/

//Query Builder

In my case, 

http://localhost:4502/bin/querybuilder.json?type=nt:file&nodename=*.jar&orderby=@jcr:content/jcr:las...

and http://localhost:4502/bin/querybuilder.json?type=nt:file&nodename=*.jar&orderby=@jcr:content/jcr:las...

are working fine.

~kautuk

[henrikh65523654]

Hi, thanks for your response, and for the link to the community article. It was very useful! 
I have tested both your queries, and they work perfectly for me as well. But when I do some minor adjustments (querying for the content we look for): 

http://localhost:4502/bin/querybuilder.json?path=/content/sites/customer&type=cq:PageContent&orderby...
http://localhost:4502/bin/querybuilder.json?path=/content/sites/customer&type=cq:PageContent&orderby...

Gives "unordered" results like: 

{  
   "success":true,
   "results":10,
   "total":688,
   "more":false,
   "offset":0,
   "hits":[  
      {  
         "path":"/content/sites/customer/bedrift/nettbank/jcr:content",

         "lastModified":"2015-12-08 15:24:14",
         "created":"2016-10-03 19:45:33"
      },
      {  
         "path":"/content/sites/customer/bedrift/produkter/forsikring/ansvar/styreansvarsforsikring/jcr:content",

         "lastModified":"2015-12-08 15:24:23",
         "created":"2016-10-03 19:45:37"
      },
      {  
         "path":"/content/sites/customer/bedrift/produkter/forsikring/ansvar/ansvarsforsikring/jcr:content",

         "lastModified":"2015-12-08 15:24:23",
         "created":"2016-10-03 19:45:37"
      },
      {  
         "path":"/content/sites/customer/bedrift/produkter/forsikring/ansvar/jcr:content",

         "lastModified":"2015-12-08 15:24:22",
         "created":"2016-10-03 19:45:37"
      },
      {  
         "path":"/content/sites/customer/bedrift/produkter/forsikring/ansvar/kriminalitetsforsikring/jcr:content",

         "lastModified":"2015-12-08 15:24:23",
         "created":"2016-10-03 19:45:37"
      },
      {  
         "path":"/content/sites/customer/bedrift/produkter/forsikring/eiendom/prosjektforsikring/jcr:content",

         "lastModified":"2015-12-08 15:24:24",
         "created":"2016-10-03 19:45:38"
      },
      {  
         "path":"/content/sites/customer/bedrift/produkter/forsikring/eiendom/boligbyggforsikring/jcr:content",

         "lastModified":"2015-12-08 15:24:24",
         "created":"2016-10-03 19:45:38"
      },
      {  
         "path":"/content/sites/customer/bedrift/produkter/forsikring/eiendom/naringsbyggforsikring/jcr:content",

         "lastModified":"2015-12-08 15:24:24",
         "created":"2016-10-03 19:45:38"
      },
      {  
         "path":"/content/sites/customer/bedrift/produkter/forsikring/eiendom/jcr:content",

         "lastModified":"2015-12-08 15:24:24",
         "created":"2016-10-03 19:45:38"
      },
      {  
         "path":"/content/sites/customer/bedrift/produkter/forsikring/landbruk/jcr:content",

         "lastModified":"2015-12-08 15:24:27",
         "created":"2016-10-03 19:45:39"
      }
   ]
}

Cant understand why it sorts differently my queries....(Seems like its just being sorted on path)
Best regards

Henrik Heiestad

[Peter_Puzanovs]

Dear Herick,

Thank you for asking this question,

Your query would start working, given you try to to remove the @jcr:content/, as whenever you are looking at PageContent you are already inside the jcr:content node itself.

http://localhost:4502/bin/querybuilder.json?path=/content/sites/customer&type=cq:PageContent&orderby...

http://localhost:4502/bin/querybuilder.json?path=/content/sites/customer&type=cq:PageContent&orderby...

KInd Regards,

Peter

[henrikh65523654]

Hi,

thanks for your response. Tested the changes, but it gives same result (asc and desc both gives same result). Tested 

/bin/querybuilder.json?path=/content/sites/customer&type=cq:PageContent&orderby=@jcr:lastModified&orderby.sort=desc
/bin/querybuilder.json?path=/content/sites/customer&type=cq:PageContent&orderby=@jcr:lastModified&orderby.sort=asc
/bin/querybuilder.json?path=/content/sites/customer&type=cq:PageContent&orderby=jcr:lastModified&orderby.sort=desc

But typing nonexisting field, causes query to fail
/bin/querybuilder.json?path=/content/sites/customer&type=cq:PageContent&orderby=whatever&orderby.sort=desc

Querying for other types of content, seems to work, like nt:unstructured
/bin/querybuilder.json?path=/content/sitescustomer&type=nt:unstructured&orderby=@jcr:lastModified&orderby.sort=asc

Best regards

Henrik

[Peter_Puzanovs · Accepted solution]

Dear Henrick,

Have looked again at your question,

Checked index in /oak:index/cqPageLucene/aggregates/cq:PageContent, it is exactly same as 6.1

According to CRX/DE shouldn't you be sorting on @cq:lastModified?

http://localhost:5555/bin/querybuilder.json?path=/content/geometrixx/en&type=cq:PageContent&orderby=...

{

• "success": true,
• "results": 10,
• "total": 59,
• "more": false,
• "offset": 0,
• "hits": [
  • {
    • "path": "/content/geometrixx/en/blog/jcr:content",
    • "excerpt": "",
    • "name": "jcr:content",
    • "title": "GeoBlog",
    • "lastModified": "2015-09-10 13:57:52",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/products/mandelbrot/jcr:content",
    • "excerpt": "Benoît Mandelbrot, who studied and popularized it. ",
    • "name": "jcr:content",
    • "title": "Mandelbrot Set",
    • "lastModified": "2015-04-01 17:25:02",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/products/circle/jcr:content",
    • "excerpt": "circle (especially when referring to its length). ",
    • "name": "jcr:content",
    • "title": "Circle",
    • "lastModified": "2015-04-01 17:25:02",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/products/square/jcr:content",
    • "excerpt": "angles of such a square are larger than a right angle. ",
    • "name": "jcr:content",
    • "title": "Square",
    • "lastModified": "2015-04-01 17:25:02",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/products/triangle/jcr:content",
    • "excerpt": "for each vertex) of any triangle is 360 degrees. ",
    • "name": "jcr:content",
    • "title": "Triangle",
    • "lastModified": "2015-04-01 17:25:02",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/community/jcr:content",
    • "excerpt": "",
    • "name": "jcr:content",
    • "title": "Community",
    • "lastModified": "2015-04-01 17:24:51",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/jcr:content",
    • "excerpt": "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Nunc eget neque. Nunc condimentum ipsum et orci. Aenean est. Cras eget diam. read more",
    • "name": "jcr:content",
    • "title": "English",
    • "lastModified": "2014-04-22 10:11:24",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/support/customersurvey/jcr:content",
    • "excerpt": "Please take a few moments to complete this satisfaction survey. ",
    • "name": "jcr:content",
    • "title": "Customer Satisfaction Survey",
    • "lastModified": "2011-11-14 20:13:28",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/company/press/asseteditor/jcr:content",
    • "excerpt": "",
    • "name": "jcr:content",
    • "title": "Press Center Asset Editor",
    • "lastModified": "2011-03-11 17:44:11",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/company/press/assetviewer/jcr:content",
    • "excerpt": "",
    • "name": "jcr:content",
    • "title": "Press Center Asset Viewer",
    • "lastModified": "2011-03-11 17:43:06",
    • "created": "2016-05-12 14:21:21"
    }
  ]

}

http://localhost:5555/bin/querybuilder.json?path=/content/geometrixx/en&type=cq:PageContent&orderby=...

{

• "success": true,
• "results": 10,
• "total": 59,
• "more": false,
• "offset": 0,
• "hits": [
  • {
    • "path": "/content/geometrixx/en/toolbar/profiles/jcr:content",
    • "excerpt": "",
    • "name": "jcr:content",
    • "title": "Profiles",
    • "lastModified": "2009-11-05 19:27:13",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/toolbar/account/me/jcr:content",
    • "excerpt": "",
    • "name": "jcr:content",
    • "title": "My Geometrixx",
    • "lastModified": "2009-11-20 00:00:00",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/toolbar/feedback/jcr:content",
    • "excerpt": "Please leave a message",
    • "name": "jcr:content",
    • "title": "Feedback",
    • "lastModified": "2010-08-19 15:38:37",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/toolbar/search/jcr:content",
    • "excerpt": "",
    • "name": "jcr:content",
    • "title": "Search",
    • "lastModified": "2010-08-19 15:38:37",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/toolbar/sitemap/jcr:content",
    • "excerpt": "",
    • "name": "jcr:content",
    • "title": "Sitemap",
    • "lastModified": "2010-08-19 15:38:37",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/support/customersurvey/thankyou/jcr:content",
    • "excerpt": "Thank you for participating in this survey. Your responses help us to improve our services. We greatly appreciate your time and feedback.",
    • "name": "jcr:content",
    • "title": "Thank you",
    • "lastModified": "2010-08-25 16:14:09",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/toolbar/jcr:content",
    • "excerpt": "",
    • "name": "jcr:content",
    • "title": "Toolbar",
    • "lastModified": "2010-08-25 21:51:02",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/toolbar/profiles/passwordchange/success/jcr:content",
    • "excerpt": "",
    • "name": "jcr:content",
    • "title": "Success",
    • "lastModified": "2010-09-14 11:24:46",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/toolbar/profiles/passwordchange/jcr:content",
    • "excerpt": "",
    • "name": "jcr:content",
    • "title": "Change Password",
    • "lastModified": "2010-09-14 13:41:23",
    • "created": "2016-05-12 14:21:21"
    },
  • {
    • "path": "/content/geometrixx/en/toolbar/profiles/forgot/jcr:content",
    • "excerpt": "If you have forgotten your password please submit your e-mail address below.You will then receive an e-mail informing you how to reset your password.",
    • "name": "jcr:content",
    • "title": "Forgot Password",
    • "lastModified": "2010-09-14 15:30:05",
    • "created": "2016-05-12 14:21:21"
    }
  ]

}

(tested on my AEM6.2 and it works via @cq:lastModifed, is it a viable option?)

Seems to be producing results here for correct orderby.

BTW, for up to date documentation look here[1]

[1] http://jackrabbit.apache.org/oak/docs/query/lucene.html

Regards,

Peter