Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
3.0k views
in Technique[技术] by (71.8m points)

azure - How to find duplicates in a nested array in cosmos db without GROUP BY and COUNT

I am trying to find duplicates in a nested object in a collection. In ye olde SQL, I would do this with some sort of GROUP BY and a COUNT. Cosmos DB doesn't support GROUP BY (as far as I can see) so I am trying to find a work around. One limitation is that I only have access to the data explorer in the azure portal (Don't ask).

To explain in more detail, suppose you have a collection like the following. Note that the first item has a duplicate in the "stuff" collection:

[
    {
        "id": "1",
        "Name": "Item with duplicate stuff",
        "stuff" : [
            {
                "name" : "A",
            },
            {
                "name" : "B",
            },
            {
                "name" : "A"
            }  
        ]
    },
    {
        "id": "2",
        "Name": "Item with unique stuff",
        "stuff" : [
            {
                "name" : "A",
            },
            {
                "name" : "B",
            },
            {
                "name" : "C"
            }  
        ]
    }    

I want to find all the items in my collection that have duplicates in the "stuff" property. So in this case it would return the item with id "1". Something like this would do nicely:

[
    {
        "id": "1"
    } 
] 

Nothing I have tried has worked and is unfit to show here.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Cosmos db supports subqueries and DISTINCT keyword. So, something like this should work

  SELECT n2
    FROM c
    JOIN (SELECT DISTINCT value s.name FROM s IN c['stuff'])  n2

result on first item

[
    {
        "n2": "A"
    },
    {
        "n2": "B"
    },
    {
        "n2": "C"
    }
]

Ref: https://docs.microsoft.com/en-gb/azure/cosmos-db/sql-query-subquery

P.S. Also, Cosmos db now supports Group By https://docs.microsoft.com/en-gb/azure/cosmos-db/sql-query-group-by


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...