CosmosDB: Group By + Join

Question

Been having trouble joining grouped data to the source data. This would be easy with relational SQL, but been spending hours trying to do this with CosmosDB SQL API with no success. Any suggestions would be greatly appreciated!

Here is the source document:

[
   {
      "stream":{
         "id":"L1",
         "version":1,
         "versionName":"abc1"
      }
   },
   {
      "stream":{
         "id":"L1",
         "version":2,
         "versionName":"abc2"
      }
   },
   {
      "stream":{
         "id":"L2",
         "version":1,
         "versionName":"xyz1"
      }
   },
   {
      "stream":{
         "id":"L2",
         "version":2,
         "versionName":"xyz2"
      }
   },
   {
      "stream":{
         "id":"L2",
         "version":3,
         "versionName":"xyz3"
      }
   }
]

Here is the goal (grouped by id):

[
   {
      "id":"L1",
      "versions":[
         {
            "version":1,
            "versionName":"abc1"
         },
         {
            "version":2,
            "versionName":"abc2"
         }
      ]
   },
   {
      "id":"L2",
      "versions":[
         {
            "version":1,
            "versionName":"xyz1"
         },
         {
            "version":2,
            "versionName":"xyz2"
         },
         {
            "version":3,
            "versionName":"xyz3"
         }
      ]
   }
]

I wonder if this is can be done or if it can only be joined together in javascript after the query results are returned?

Jay Gong Jay Gong · Accepted Answer · 2020-05-25T02:13:20

I wonder if this is can be done or if it can only be joined together in javascript after the query results are returned?

I'm afraid this is not supported in cosmos db. The sql of no-sql db is very different from sql of relational db. Aggregating the data into an array is not any one aggregation method which means it can't be used with group by.

JOIN is also different. It is used in cosmos db for joining nested array, not for joining horizontal data.

So, i think this could to be implemented with code, sort the data with order by id, then loop the results to complete the goal.

CosmosDB: Group By + Join

1 Answers