Querying nested array in cosmos db

Question

Having list of documents as below mentioned.

Document1:

 {
    id:1,
    PostList:[
     {
         postname:"aaa",
         lastdatetime:2017-07-13T17:10:25+05:30,
         sname:"sas"
     },
     {
         postname:"aaa1",
         lastdatetime:2017-07-14T17:10:25+05:30,
         sname:"sasadd"
     },
     {
         postname:"aaa2",
         lastdatetime:2017-07-10T17:10:25+05:30,
         sname:"weq"
     }
     ]
}

Document2:

 {
        id:2,
        PostList:[
         {
             postname:"aaa",
             lastdatetime:2017-07-13T17:10:25+05:30,
             sname:"sas"
         },
         {
             postname:"aaa1",
             lastdatetime:2017-07-14T17:10:25+05:30,
             sname:"sasadd"
         },
         {
             postname:"aaa2",
             lastdatetime:2017-07-10T17:10:25+05:30,
             sname:"weq"
         }
         ]
}

I need a list of postnames which is equal to "aaa" with orderby lastdatetime.

I am able to get query

select f.lastdatetime,f.postname
from c 
join f in c.PostList 
where f.postname='aaa' 

But I need to get the list with orderby lastdatetime.

When I try the below query, I am getting an error

Order-by over correlated collections is not supported

select f.lastdatetime,f.postname
from c 
join f in c.PostList 
where f.postname='aaa' ORDER BY f.lastdatetime ASC

Does anybody have an idea to get through?

Answer 1

As @Rafat Sarosh said in the comment :Order-by over correlated collections is not supported and it will be enable in the future.

However, I suggest a workaround for you to track for your solution: use Azure Cosmos DB UDF.

You could pass the results of your query as parameters to the UDF for sorting processing.

Query Sql:

select f.lastdatetime,f.postname
 from c 
join f in c.PostList 
where f.postname='aaa' 

UDF sample code:

function userDefinedFunction(arr){
    var i = arr.length, j;
    var tempExchangVal;
    while (i > 0) {
        for (j = 0; j < i - 1; j++) {
            if (arr[j].lastdatetime < arr[j + 1].lastdatetime) {
                tempExchangVal = arr[j];
                arr[j] = arr[j + 1];
                arr[j + 1] = tempExchangVal;
            }
        }
        i--;
    }
    return arr;
}

Hope it helps you.