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?

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 } ]