Get unique array values across multiple CosmosDB documents

Question

I have documents with a "Cedent" field that is an array of strings. When I run this query:

SELECT DISTINCT c.Cedents FROM c

I get the following result:

[
    {
        "Cedents": [
            "Test 1",
            "Test 2"
        ]
    },
    {
        "Cedents": [
            "Test 1",
            "Test 3"
        ]
    }
]

Is there a way to get only the unique values across all documents, as a single array? Example:

[
    {
        "Cedents": [
            "Test 1",
            "Test 2",
            "Test 3"
        ]
    }
]

juunas juunas · Accepted Answer · 2021-03-31T09:48:12

This works for example:

SELECT DISTINCT VALUE x
FROM x IN c.Cedents

This iterates each array element and gets the distinct values.

Result:

[
    "Test 1",
    "Test 2",
    "Test 3"
]

Documentation: https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-object-array#Iteration

Get unique array values across multiple CosmosDB documents

1 Answers