error: SC3020. The SQL query text exceeded the maximum limit of 544720 characters in Azure CosmosDB

Question

error: SC3020. The SQL query text exceeded the maximum limit of 544720 characters in Azure CosmosDB

dmitrii sablin 0

Hello, I am using Azure Cosmos DB MongoDB shell, and when I perform the QUERY, I receive the error: The SQL query text exceeded the maximum limit of 544720 characters in Azure CosmosDB.

My query is the following:

db.Actions.aggregate([{$match:{type:{$in:["registration","fillform"]}}},{$lookup:{from:"Visits",localField:"value",foreignField:"token",as:"Visit"}},{$unwind:"$Visit"},{$match:{"Visit.url":{$regex:/path=testtest/}}},{$group:{_id:{Date:{$dateToString:{format:"%Y-%m-%d",date:"$date"}},ActionType:"$type"},Count:{$sum:1}}},{$project:{_id:0,Date:"$_id.Date",ActionType:"$_id.ActionType",Count:1}}])

the source C# code via mongo driver the following:

  
   var actions = await _dbContext.Actions
                    .Aggregate()
                    .Match(actionsFilter)
                    .Lookup<Action, Visit, ActionLookedUp>(_dbContext.Visits,
                    (Action output) => output.Value,
                    (Visit visit) => visit.Token,
                    (ActionLookedUp output) => output.Visits)
                    .Unwind<ActionLookedUp>("Visits")
                    .Match(visitsFilter)
                    .Group(GetGroupByExpression(filter.GroupBy), g => new { g.Key, g.Key.Type, Count =                   g.Count() }).ToListAsync();

the response is also can not be more than 10 or even 50mb, because as you can see i use Group

As you can see this query is not too long (just filter, lookup, and group and that’s it) I need to receive groups with count, but I receive the Error. How can I fix it?

I did not find any answer on this issue in the internet (only something like "write to support")

Please help, how can i perform the query and receive the data?

Thanks!

1 answer

Your answer

Answer 1

SSingh-MSFT 16,371 Moderator

Hi dmitrii sablin •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

We are sorry about the inconvenience you are experiencing with the SQL query limit in Azure Cosmos DB.

The limits are defined as follows at the standard Microsoft documentation:

User's image

However, You can increase any of these SQL query limits by creating an Azure Support request.

Create new support request using the link by steps mentioned there if you have support plan for exclusive increase in limits for your Cosmos DB.

In case if you don't have a support plan, do let us know here so that we can check on other options to unblock you. Thanks

dmitrii sablin 0 Reputation points

2023-05-10T18:18:49.2633333+00:00
@SSingh-MSFT as you can see i did not violate any of these limits:

length of SQL query is very short (one select, 1 join and 1 group, and that's it)
The same things with another limitations. I can not understand, which limit i reached out?
I see only 1 relevant limitation - query memory size, but i use version 4.2 this limit is not relevant. I receive the same result even i add ".Limit(1)" after lookup

So, how can figure out, what is the reason, and what exactly limitation i need to extend, and most important: HOW i reached this limit? because query is very short, and response too.

P.S. I also wanted to ask, how SQL query text can be more then 544720 ? because this query is pretty simple and small, (something like this)

SELECT CAST(A.Date AS DATE) AS [Date], A.Type AS ActionType, COUNT(*) AS [Count] FROM Actions A INNER JOIN Visits V ON V.Token = A.Value WHERE A.Type IN ('registration', 'fillform') AND V.Url LIKE '%path=testtest%' GROUP BY CAST(A.Date AS DATE), A.Type
SSingh-MSFT 16,371 Reputation points Moderator

2023-05-11T03:48:25.7866667+00:00

Hi dmitrii sablin •,

I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you. Thanks
dmitrii sablin 0 Reputation points

2023-05-11T05:13:53.59+00:00

Hi @SSingh-MSFT i don't have it unfortunately, i also did not find any answer about this problem inside the community, looks like i am the first how faced with it
SSingh-MSFT 16,371 Reputation points Moderator

2023-05-11T08:09:06.8166667+00:00

Hi dmitrii sablin •,

Could you send an email to AzCommunity@Microsoft.com with your Subscription ID and thread link to this post, and I will enable a one-time free support request for your subscription.

Please reference this forum thread in the subject: ATTN: SHAKTI - error: SC3020. The SQL query text exceeded the maximum limit of 544720 characters in Azure CosmosDB

Regards,

@SSingh-MSFT
dmitrii sablin 0 Reputation points

2023-05-14T11:17:04.1533333+00:00

@SSingh-MSFT

I also found some interesting thing: db.collection.countDocumentsAsync(filter) works loo long (22 seconds), but db.collection.find(filter).count() works immediately. Is it means that Cosmos db does not support mongo api completely?

What i see now: the "aggregate" function almost does not work.
SSingh-MSFT 16,371 Reputation points Moderator

2023-05-15T04:22:39.6233333+00:00

Hi dmitrii sablin •,

As suggested above, kindly raise support case for investigation. Thank you.
SSingh-MSFT 16,371 Reputation points Moderator

2023-05-16T04:53:18.61+00:00

Hi dmitrii sablin •,

We have replied to your email. Please take a look for further action. Thank you.

Share via

error: SC3020. The SQL query text exceeded the maximum limit of 544720 characters in Azure CosmosDB

1 answer

Your answer