DocumentDB: How to filter document on array within array?

Question

Let's say I have the following document:

{
"Id": "1",
"Properties": [
    {
        "Name": "Name1",
        "PropertyTypes": [
            "Type1"
        ]
    },
    {
        "Name": "Name2",
        "PropertyTypes": [
            "Type1",
            "Type2",
            "Type3"
        ]
    }
]
}

When I use the following SQL:

SELECT c.Id FROM c
JOIN p in c.Properties
WHERE ARRAY_CONTAINS(p.PropertyTypes,"Type1")

I get as return:

[
{
    "Id": "1"
},
{
    "Id": "1"
}
]

How do I change my query so that it only returns distinct documents?

feedback.azure.com/forums/263030-azure-cosmos-db/suggestions/… — Buddhi
– Buddhi, Commented Oct 19, 2017 at 19:15

marc_s · Accepted Answer · 2017-11-07 21:28:43Z

1

As far as I know, Distinct hasn't supported by Azure Cosmos DB yet.

It seems that there is no way to remove the repeat data in the query SQL level.

You could handle with your query result set in the loop locally.

However, if your result data is large,I suggest you using a stored procedure to handle with result data in Azure Cosmos DB to release the pressure on your local server.

You could refer to the official tutorial about SP.

edited Nov 7, 2017 at 21:28

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 24, 2017 at 7:55

Jay Gong

23.8k2 gold badges30 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

DocumentDB: How to filter document on array within array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related