CODEWITHSUNDEEP
azureazure-cosmosdbazure-logic-appsazure-cosmosdb-sqlapi
mickl
mickl

Reputation: 49945

Azure CosmosDB Query Explorer vs Data Explorer

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:

enter image description here

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

Upvotes: 1

Views: 1665

Answers (1)

Hury Shen
Hury Shen

Reputation: 15734

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". enter image description here

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~

Upvotes: 1

Related Questions