Azure CosmosDB Query Explorer vs Data Explorer

Question

I'm running the same query against my CosmosDB instance (using SQL API):

SELECT c.partition, COUNT(1) AS total
FROM c 
WHERE c.system = "SF"
GROUP BY c.partition

I'm a bit surprised that I'm getting expected results from Data Explorer while under Query Explorer tab I'm getting 400 Bad Request with below message:

{"code":400,"body":"{\"code\":\"BadRequest\",\"message\":\"Message: {\\"Errors\\":[\\"Cross partition query only supports 'VALUE ' for aggregates.\\"]}\r\nActivityId: d8523615-c2ff-47cf-8102-5256237c7024, Microsoft.Azure.Documents.Common/2.7.0\"}","activityId":"d8523615-c2ff-47cf-8102-5256237c7024"}

I know I can use the first one but the same exception occurs when I'm trying to run my query from Logic Apps:

So the question is pretty simple: is that query syntactically correct or not ?

By Data Explorer you mean the one in Azure Portal? Which is the Query Explorer you mention? — Matias Quaranta
@MatiasQuaranta yes, both are just tabs in Azure Portal where I can run my CosmosDB query — mickl
I test it in my side, but whether test in "Data Explorer" and "Query Explorer" or in logic app, they all work fine with the sql you provided. So could you please provide some more details about your issue(such as the structure of the "c" table)? — Hury Shen
@HuryShen thank you for the time you've spent trying to solve this issue. I'm still getting 400. The container grew up recently (more than 4 GB of data) and is partitioned by partition field. My guess is that my collection got partitoned physically (not only logically) so perhaps in that case cross-partitioned queries have some limitations in QueryExplorer and Logic Aps. — mickl

Hury Shen Hury Shen · Accepted Answer · 2019-12-18T10:50:46

For your requirements, I think we can have a try to use azure function in your logic app instead of the "Query documents V2" action.

Here is my function code:

using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;

namespace HuryCosmosFun
{
    public static class Function1
    {
        [FunctionName("Function1")]
        public static IActionResult Run(
            [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
            [CosmosDB(
                databaseName: "ToDoList", 
                collectionName: "Items", 
                SqlQuery = "SELECT c.partition, COUNT(1) AS total FROM c WHERE c.system = 'SF' GROUP BY c.partition", 
                ConnectionStringSetting = "CosmosDBConnection")]
                IEnumerable<ResultsClass> results,
                ILogger log)
            {
                log.LogInformation("C# HTTP trigger function processed a request.");

                foreach (ResultsClass result in results)
                {
                    log.LogInformation(result.partition);
                    log.LogInformation(result.total.ToString());
                }
                return new OkResult();
            }
    }
}

namespace HuryCosmosFun
{
    public class ResultsClass
    {
        public string partition { get; set; }
        public int total { get; set; }
    }
}

For more information about the code above, you can refer to this tutorial.

After publish to azure, we can create an azure function in logic app, it will help us do the sql operation.

By the way, since this tutorial mentioned azure cosmos db sdk has supported "group by".

I think we can also write code in azure function to connect and query cosmos db by the sdk in this document, and then create azure function in logic app as the first solution.

Hope it would be helpful to your requirements~

Azure CosmosDB Query Explorer vs Data Explorer

1 Answers