How to write Azure Cosmos COUNT DISTINCT query

user_1857450 0
Viewed 1970

Goal:

To return the count of distinct values of a particular document field within a partition of a Cosmos collection.

Steps:

If I run the following query on my Azure Cosmos database,

SELECT DISTINCT c.field
FROM c
WHERE c.field = 'abc' AND c.partitionKeyField = '123'

I get one row as expected, e.g. the following response 

[
    {
        "field": "abc"
    }
]

However, if I then run the following query in an attempt to count the number of distinct documents in the response, via the following query

SELECT VALUE COUNT(1)
FROM (
    SELECT DISTINCT c.field
    FROM c
    WHERE c.field = 'abc' AND c.partitionKeyField = '123'
)

It returns 

[
    6
]

This is the total number of documents with c.field set to "abc" rather than the number of distinct values of c.field.

Question:

Please could you help me understand why the query returns the number of documents rather the number of distinct values for c.field, and if there is a query which will return the number of distinct values of c.field, i.e. 1?

Edit - PS. I know this a contrived example as by definition the number of unique values of c.field is always 1 - I have deliberately simplified this from the real case.

azure-cosmosdb-sqlapi azure-cosmosdb distinct
1 Answers

At the moment of writing this (22/01/2021) this query provides correct number of distinct values:

SELECT COUNT(UniqueFields) AS UniqueCount
FROM (SELECT DISTINCT c.field
    FROM c
    WHERE c.field = 'abc' AND c.partitionKeyField = '123') as UniqueFields
user_23479990
Related