CODEWITHSUNDEEP
azure-cosmosdb
n8wrl
n8wrl

Reputation: 19765

Azure Cosmos DB Query: How to concatenate arrays across docs?

Documents in my CosmosDB database all have an array element like so:

{
    id: 1,
    list: [1, 2, 3]
},
{
    id: 2,
    list: [2, 3, 4]
},
...

I want a query that concatenates the array "list" across all documents (ideally, I'd like a UNIQUE concatenation but I can start with all of them).

The result I want is:

[1, 2, 3, 2, 3, 4]

or even better:

[1, 2, 3, 4]

This "feels" like an aggregate but I can't seem to get my head around it. Any ideas?

I know I can load each document in turn and iterate. I was hoping to do this with a query and avoid the expense of loading every document in turn.

Upvotes: 1

Views: 5987

Answers (2)

gunjan garg
gunjan garg

Reputation: 31

You can use 'Distinct', it is supported now:

SELECT distinct value i FROM c join i in c.list

Results in: [1, 2, 3, 4]

Upvotes: 3

Imre Pühvel
Imre Pühvel

Reputation: 4994

Using SQL query

You can select individual values from within array by joining and merge the resulting values back into single array by using the value keyword:

SELECT value i
FROM c
join i in c.list

Note that this returns all values in a single array but unfortunately including duplicates.

To my knowledge there is currently no server-side distinct capability. But there is a request of "Provide support for DISTINCT" with 1k+ votes, so I believe it will be possible at some point in the future. If this is something you care about, then go and upvote.

Using SP

Alternative way of getting unique list made on server-side would be to use Stored Procedures within you documentDB collection:

function GetUniqueItems() {
    var collection = getContext().getCollection();

    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT value i FROM c join i in c.list',
        function (err, feed, options) {
            if (err) throw err;

            var uniqueArray = feed
                .filter(function(item, pos) { return feed.indexOf(item) == pos;});
            getContext().getResponse().setBody(uniqueArray);
        });

    if (!isAccepted) throw new Error('The query was not accepted by the server.');
}

You can read more how to utilize DocumentDB serverside programming from "Azure Cosmos DB server-side programming: Stored procedures, database triggers, and UDFs".

Upvotes: 4

Related Questions