如何在没有GROUP BY和COUNT的情况下在cosmos db中的嵌套数组中查找重复项 [英] How to find duplicates in a nested array in cosmos db without GROUP BY and COUNT

问题描述

我正在尝试在集合的嵌套对象中查找重复项.在旧的SQL中，我将使用某种GROUP BY和COUNT来执行此操作. Cosmos DB不支持GROUP BY(据我所知)，因此我试图找到解决方法.一个局限性是我只能访问azure门户中的数据浏览器(不要问).

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

我想找到集合中所有在东西"属性中重复的项.因此，在这种情况下，它将返回ID为"1"的项目.这样的事情会做得很好:

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.

推荐答案

Cosmos db支持子查询和DISTINCT关键字.所以，这样的事情应该起作用

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

第一项结果

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

参考: https://docs.microsoft.com/zh- gb/azure/cosmos-db/sql-query-subquery

P.S.此外，Cosmos db现在支持分组依据 https://docs.microsoft.com/zh-CN/azure/cosmos-db/sql-query-group-by

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

这篇关于如何在没有GROUP BY和COUNT的情况下在cosmos db中的嵌套数组中查找重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！