Querybuilder.json: Orderby works only for some properties | Community
Skip to main content
H
HenrikHe
Level 2
October 26, 2016
Solved

Querybuilder.json: Orderby works only for some properties

  • October 26, 2016
  • 5 replies
  • 4135 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Peter_Puzanovs

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=@cq:lastModified&orderby.sort=desc

{
  • "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=@cq:lastModified&orderby.sort=asc

{

  • "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

5 replies

H
HenrikHeAuthor
Level 2
October 31, 2016

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=@jcr:content/jcr:lastModified&orderby.sort=desc
http://localhost:4502/bin/querybuilder.json?path=/content/sites/customer&type=cq:PageContent&orderby=@jcr:content/jcr:lastModified&orderby.sort=asc

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
Community Advisor
Peter_PuzanovsCommunity Advisor
Community Advisor
October 31, 2016

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=@jcr:lastModified&orderby.sort=desc

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

KInd Regards,

Peter

H
HenrikHeAuthor
Level 2
October 31, 2016

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
Community Advisor
Peter_PuzanovsCommunity AdvisorAccepted solution
Community Advisor
October 31, 2016

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=@cq:lastModified&orderby.sort=desc

{
  • "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=@cq:lastModified&orderby.sort=asc

{

  • "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

Terms & ConditionsAccessibility statement

Loading footer...