CODEWITHSUNDEEP
azureazure-cosmosdbazure-cosmosdb-sqlapi
Michael Böckling
Michael Böckling

Reputation: 7872

Get unique array values across multiple CosmosDB documents

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

Upvotes: 0

Views: 1408

Answers (1)

juunas
juunas

Reputation: 58733

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://learn.microsoft.com/en-us/azure/cosmos-db/sql-query-object-array#Iteration

Upvotes: 2

Related Questions