Getting grouped (distinct?!?) array of values from Cosmos SQL

Question

Given a collection of Cosmos documents similar to the following, I'd like to generate a grouped (distinct?!?) list of "categories" using Cosmos SQL. Any help in this regard would be greatly appreciated.

[
  {
    "id": "f0136e76-8e66-6a5a-3790-b577001d6420",
    "itemId": "analyze-and-visualize-your-data-with-azure-cosmos-db-notebooks",
    "title": "Built-in Jupyter notebooks in Azure Cosmos DB are now available",
    "categories": [
      "Developer",
      "Database",
      "Data Science"
    ]
  },
  {
    "id": "f0136e76-8e66-6a5a-3790-b577001d6420",
    "itemId": "analyze-and-visualize-your-data-with-azure-cosmos-db-notebooks",
    "title": "Built-in Jupyter notebooks in Azure Cosmos DB are now available",
    "categories": [
      "Developer",
      "Database",
      "Data Science"
    ]
  },
  {
    "id": "d98c1dd4-008f-04b2-e980-0998ecf8427e",
    "itemId": "improving-azure-virtual-machines-resiliency-with-project-tardigrade",
    "title": "Improving Azure Virtual Machines resiliency with Project Tardigrade",
    "categories": [
      "Virtual Machines",
      "Supportability",
      "Monitoring"
    ]
  }
]

Sajeetharan Sajeetharan · Accepted Answer · 2019-10-13T04:04:27

GroupBY is not supported by Azure CosmosDB so far. You can alternatively use Stored Procedure to implement your requirement.

Base on the sample documents you have given above, here is a sample stored Procedure

function groupBy() {
    var collection = getContext().getCollection();
    var collectionLink = collection.getSelfLink();
    var isValid = collection.queryDocuments(
        collectionLink,
        'SELECT * FROM stackoverflow s',
        {EnableCrossPartitionQuery: true},
    function (err, feed, options) {
        if (err) throw err;
        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            console.log(JSON.stringify(response));
            response.setBody('no docs found');
        }
        else {
            var response = getContext().getResponse();
            var items = {};
             for(var i=0;i<feed.length;i++){
                 var categories = feed[i].categories;
                 for(var j=0;j<categories.length;j++){
                        items[categories[j]] = categories[j]
                 }
                 var distinctArray = [];
                 for(var distinctObj in items){
                    distinctArray.push(items[distinctObj]);
                }
             }
            response.setBody(distinctArray);
        }
    });

    if (!isValid) throw new Error('Kindly check your query, which not accepted by the server.');
}

Getting grouped (distinct?!?) array of values from Cosmos SQL

1 Answers