Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

Querybuilder.json: Orderby works only for some properties

Avatar

Level 2

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

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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

View solution in original post

5 Replies

Avatar

Level 2

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

Avatar

Community Advisor

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

Avatar

Level 2

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

Avatar

Correct answer by
Community Advisor

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