Share via

Getting random results when querying ComosDb

Volodymyr Kovalenko 21 Reputation points
2022-11-01T12:28:40.907+00:00

When I execute this query in Cosmos. It always gives the random result.

It could be a bug in the engine. So I"m wondering if I could report it somehow to the team?
Or prolly get an advise on why it could be working this way

NOTE: when uncommenting filter for BillingItemId It becomes stable. But in my case I'm not ok with it since I want couple more Counters to be fethced within that same query.

See query attached as well as link to the Json Collection, could be imported
https://drive.google.com/file/d/17MGMvmwpQy0_SiRBV75hqzsWiBsNencP/view?usp=share_link

SELECT
SUM(d.TotalCartSubscriptionCount1) AS TotalCartSubscriptionCount1,
SUM(d.TotalCartSubscriptionCount2) AS TotalCartSubscriptionCount2
FROM(
SELECT
MAX(c.BillingItemId = '7f5c106f-6f90-415c-b45e-a87b8a7c5fc9' ? c.Quantity['Value'] : 0) AS TotalCartSubscriptionCount1 ,
MAX(c.BillingItemId = '68167806-c313-41b9-8d94-5f88bb07ff8b' ? c.Quantity['Value'] : 0) AS TotalCartSubscriptionCount2
FROM c
WHERE c.BillingPeriodStartDate >= '2022-06-01T21:00:00.0000000Z'
AND c.BillingPeriodEndDate <= '2022-09-30T20:59:59.0000000Z'
AND c.CompanyId IN('518b7bad-fd34-42be-8071-1932449d399a', 'a537c385-3cd2-4639-b87a-471f85866d58')
-- AND c.BillingItemId IN('68167806-c313-41b9-8d94-5f88bb07ff8b', '7f5c106f-6f90-415c-b45e-a87b8a7c5fc9', '340b529c-577f-4b9f-bac9-f4075348546e')
GROUP BY c.CompanyId ) d

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,901 questions
Accepted answer
  1. Sajeetharan 2,261 Reputation points Microsoft Employee
    2022-11-02T17:44:50.103+00:00

    Hi , please note that queries with an aggregate system function (SUM in this case from the outer SELECT) and a subquery with GROUP BY aren't supported. This is documented here- GROUP BY clause in Azure Cosmos DB | Microsoft Learn. They're getting inconsistent results because the query can't be distributed properly.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator
    2022-11-03T12:38:47.547+00:00

    Hi, @Volodymyr Kovalenko Thanks for checking with one PartitionKey
    As @Sajeetharan mentioned subquery with GROUP BY isn't supported.

    Workaround: In order to aggregate effectively and efficiently you could use materialized view to aggregate objects into another container, here is a 3rd party code as an example

    Regards
    Geetha

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.