How to order N1QL results based on sub-array value of ARRAY_AGG

Question

I have the following N1QL query:

 SELECT COUNT(tutorial) AS rows,
   ARRAY_AGG([age,fname])[0:3] AS res
      FROM tutorial

Can I order the child arrays of res by age value in DESC I tried all of the below with noting change of the order

ORDER BY res[*].age DESC
ORDER BY res[*][age] DESC
ORDER BY age DESC
ORDER BY res.age DESC

Also you can execute the preceding query directly on following link to see the main structure of document and results http://query.pub.couchbase.com/tutorial/#3

Answer 1

Old question but I was facing the same problem.

My answer is specific to your need for sorting by DESC values.

ARRAY_SORT() only appears to work for assending order - there doesn't seem to be any option for DESC.

However, I found there is also ARRAY_REVERSE()

So when I combined these two I got the desired result, eg:

...
ARRAY_REVERSE( ARRAY_SORT( yourArrayNeeds ) ) AS result
...

Additionally, something else that tripped me up was that the item to sort by with ARRAY_SORT() needed to be alphabetically first in the array list. So, for example, if wishing to sort by 'item.submitted':

THIS DIDN'T SORT AS DESIRED:

...
ARRAY_SORT({ "submitted":item.submitted, "name":item.name }) AS result
...

THIS WORKED:

...
ARRAY_SORT({ "_submitted":item.submitted, "name":item.name }) AS result
...

...and then if you want a DESC order:

...
ARRAY_REVERSE( ARRAY_SORT({ "_submitted":item.submitted, "name":item.name }) ) AS result
...