Objects and arrays defined in outer scopes are currently not supported inside sortBy predicate

Question

This problem was raised in 2018 and it's still not fixed: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e5d1d787-73b5-4cd6-9954-6cd3ab23b3fe/objects-and-arrays-defined-in-outer-scopes-are-currently-not-supported-inside-sortby-predicate?forum=azurecosmosdb

Are there plans to fix this? If not what's the work-around?

CosmosDB does not permit a very simple and common scenario

fuction (request) {

        // Filter
        __.chain()
            .sortByDescending(function(item){ return item._ts})
            .filter(item => {

               return item.userId === request.userId

            })
            .value();

This is errorring out with

The variable 'request' is not supported. Objects and arrays defined in outer scopes are currently not supported inside sortBy predicate.

Any idea when this is going to be fixed?

Thanks

Answer

@Noel Abrahams

We have received the reply from the Product team:

For performance reasons, we don’t recommend executing queries through stored procedures. You can learn more about the use cases for stored procedures here: https://video2.skills-academy.com/en-us/azure/cosmos-db/stored-procedures-triggers-udfs#benefits-of-using-server-side-programming. Stored procedures are optimized for write-performance, not read performance. We recommend running queries through the SDK.

In this specific case, if you wanted to run this query through a stored procedure, here is a sample workaround:

Original:

fuction (request) {  
        __.chain()  
            .sortByDescending(function(item){ return item._ts})  
            .filter(item => {  
  
               return item.userId === request.userId  
  
            })  
            .value();

Workaround:

function(request) {  
  var inputUserId = request.userId;   
  __.chain()  
    .filter(item => item.userId === inputUserId)  
    .sortByDescending(item => item._ts)  
    .value();  
}

Hope this helps.

Please get back to us for any further questions.

Regards
Navtej S

Share via

Objects and arrays defined in outer scopes are currently not supported inside sortBy predicate

1 answer

Your answer