Issue with group by in Cosmos DB

Question

I am trying to group by the first element of an array in my documents.

And then filter so I only get groups with a value larger than 1. I am looking for duplicates.

The documents look like this:

    {
        "Entity": {
            "EntityId": 118788,
            "Urls": [
                {
                    Value: "http://some/url"
                }
            ]
        }
    }

This query works fine:

SELECT c.Entity.Urls[0]["Value"] as url, count(1) as cnt
FROM c
where IS_DEFINED(c.Entity.Urls) AND ARRAY_LENGTH(c.Entity.Urls) > 0
group by c.Entity.Urls[0]["Value"]

But how do I filter my groups to only get those where cnt > 1? Or alternatively sort by cnt descending.

The following is giving me an error:

select * from 
(
    SELECT c.Entity.Urls[0]["Value"] as url, count(1) as cnt
    FROM c
    where IS_DEFINED(c.Entity.Urls) AND ARRAY_LENGTH(c.Entity.Urls) > 0
    group by c.Entity.Urls[0]["Value"]
) 
where cnt > 1

Answer 1

Try below code.

select  *  from
(
SELECT c.Entity.Urls[0]["Value"] as  url, count(1) as cnt
FROM c
where IS_DEFINED(c.Entity.Urls) AND ARRAY_LENGTH(c.Entity.Urls) >  0
group  by c.Entity.Urls[0]["Value"]
) c where c.cnt>1

Here you need to give a name to the result of group by query to access the column. I have given c to the result of first query and used as c.cnt while doing query.