How to use HAVING in CosmosDB

Question

I am trying to use the following query to see if I have duplicates in the DB

SELECT c.VariantNo, count(1) AS jongel FROM c where c.brand = 'XXXX' AND c.Consumer = 'XXX_V2' GROUP BY c.VariantNo HAVING jongel > 1

But I am getting a syntax error close to HAVING

How can I found if I have more than one document with the same VariantNo?

Dick

Answer 1

Per my experience,HAVING is not supported.

AS a workaround,you can use this sql:

SELECT d.VariantNo,d.jongel from (Select COUNT(1) AS jongel,c.VariantNo from c where c.brand ='cx' and c.Consumer = 'gx' group by c.VariantNo) d where d.jongel > 1

Below is my test data:

[ { "id": "1", "VariantNo": 2, "brand": "cx", "Consumer": "gx" }, { "id": "2", "VariantNo": 3, "brand": "cx", "Consumer": "gx" }, { "id": "3", "VariantNo": 2, "brand": "cx", "Consumer": "gx" }, { "id": "4", "VariantNo": 3, "brand": "cx", "Consumer": "gx" }, { "id": "5", "VariantNo": 6, "brand": "cx", "Consumer": "gx" }
]

Here is the output:

[ { "VariantNo": 2, "jongel": 2 }, { "VariantNo": 3, "jongel": 2 } ]

Bora

This works, the only downside is the huge performance toll on large datasets – Verbena

actually this doesn't seem to always work, at least I have examples of it not working. My theory is that at least some of the grouping is done client-side in the query tool, probably due to data partitioning, so pushing the having clause to the server results in rows being omitted because they didn't meet the criteria BEFORE the grouping was fully calculated – Ostrom

Answer 2

Cosmos DB doesn't support non-correlated subqueries

Fray