Azure CosmosDB using Mongo Drivers: Get Count With out getting all documents based on element in sub sub document in C#.Net

Question

Below is my JSON structure which I'm storing in Azure MongoDB(Pasted the sample structure), I want to query using userID element which is present in Plyrs (Sub Sub document) to get list of all tournaments user has played.

1. I have tried with Aggregate() but not succeeded. Please correct me if I'm missing some thing, below is the code for it.

2. Tried to get using builders but not succeeded, using builders I'm able to retrieve up to one level (sub collection , with json(pasted below) reference upto tbls ) is there any way that I can query on Plyrs(sub sub collection) .

3. Will Azure MongoDB support Aggregate() or the count with out getting the whole documents?

Please guide me Aggregation

   var count = sampleMongoDataBase.GetCollection<GameInfo>(sampleCollectionName).Aggregate()
                    .Unwind<GameInfo, TableDocumet>(t => t.Tables)
                    .Unwind<TableDocumet, PlayerDocument>(p => p.Players)
                   .Match(x => x.UserID == "3fd30fa8-f5c5-4311-8741-4032142bbb33")
                   .Group(new MongoDB.Bson.BsonDocument { { "id", "$Plyrs._id" }, { "count", new MongoDB.Bson.BsonDocument("$sum", 1) } }); 

Builders

 mongoDataBase.GetCollection<GameInfo>(mongoCollectionName).Find(Builders<GameInfo>.Filter.ElemMatch(x => x.Tables,t => t.TableId == 1)).Count();

Error : Command aggregate failed: '$group' is not supported.

JSON :

`

{
"TnId": 651,
"QnId": "7a5de5dc-b02f-4013-9d02-ebf8430e7a56",
"samt": 10,
"tbls": [
  {
    "TblId": 1,
    "Plyrs": [
      {
        "_id": 1048995,
        "UserId": "FC381A6D717B6973-711BD743AE90E6D7",
        "St": 3,
        "PCS": 2,
        "ConnId": null,
        "DN": "Roman Yates",
        "RB": 2,
        "LL": 1,
        "Chips": 5000,
        "Rank": 46,
        "ExitLvl": 0,
        "TRA": 0,
        "PRI": "",
        "IsAns": false
      },
      {
        "_id": 1048996,
        "UserId": "fc3e0971-2c3c-e697-b68a-33b12a207bc2",
        "St": 3,
        "PCS": 2,
        "ConnId": null,
        "DN": "Neil Gaines",
        "RB": 2,
        "LL": 1,
        "Chips": 5000,
        "Rank": 46,
        "ExitLvl": 0,
        "TRA": 0,
        "PRI": "",
        "IsAns": false
      },
      {
        "_id": 1048997,
        "UserId": "FC369812-6DC04066-A133-1C10EEBA1546",
        "St": 3,
        "PCS": 2,
        "ConnId": null,
        "DN": "Lori Reyes",
        "RB": 2,
        "LL": 1,
        "Chips": 5000,
        "Rank": 46,
        "ExitLvl": 0,
        "TRA": 0,
        "PRI": "",
        "IsAns": false
      },
      {
        "_id": 1048998,
        "UserId": "FC2AC461-C7FAD800D3D79A2CD2680C72",
        "St": 3,
        "PCS": 2,
        "ConnId": null,
        "DN": "Duane Hoffman",
        "RB": 2,
        "LL": 1,
        "Chips": 5000,
        "Rank": 46,
        "ExitLvl": 0,
        "TRA": 0,
        "PRI": "",
        "IsAns": false
      },
      {
        "_id": 1048999,
        "UserId": "FC39AFCF-688C-B61A-A0DB-80EADAC832CD",
        "St": 3,
        "PCS": 2,
        "ConnId": null,
        "DN": "Alfredo Ware",
        "RB": 2,
        "LL": 1,
        "Chips": 5000,
        "Rank": 46,
        "ExitLvl": 0,
        "TRA": 0,
        "PRI": "",
        "IsAns": false
      },
      {
        "_id": 1049000,
        "UserId": "FC2B863D-9ACC-13C706EA-8E31917566DB",
        "St": 3,
        "PCS": 2,
        "ConnId": null,
        "DN": "Abigail Riddle",
        "RB": 2,
        "LL": 1,
        "Chips": 5000,
        "Rank": 46,
        "ExitLvl": 0,
        "TRA": 0,
        "PRI": "",
        "IsAns": false
      }
    ]
  },
  {
    "TblId": 2,
    "Plyrs": [
      {
        "_id": 1049001,
        "UserId": "FC2BCC93-DDAE-4622-8D1226BDF1181D63",
        "St": 3,
        "PCS": 2,
        "ConnId": null,
        "DN": "Rene Spence",
        "RB": 2,
        "LL": 1,
        "Chips": 5000,
        "Rank": 46,
        "ExitLvl": 0,
        "TRA": 0,
        "PRI": "",
        "IsAns": false
      },
      {
        "_id": 1049002,
        "UserId": "FC3D0CF42017-C3BFF460-E8DBDE3D1D77",
        "St": 3,
        "PCS": 2,
        "ConnId": null,
        "DN": "TU006918",
        "RB": 2,
        "LL": 1,
        "Chips": 5000,
        "Rank": 46,
        "ExitLvl": 0,
        "TRA": 0,
        "PRI": "",
        "IsAns": false
      },
      {
        "_id": 1049003,
        "UserId": "FC2CDE495F34E557-77C8-4B10981B7758",
        "St": 3,
        "PCS": 2,
        "ConnId": null,
        "DN": "Marshall Lutz",
        "RB": 2,
        "LL": 1,
        "Chips": 5000,
        "Rank": 46,
        "ExitLvl": 0,
        "TRA": 0,
        "PRI": "",
        "IsAns": false
      },
      {
        "_id": 1049004,
        "UserId": "FC3EE1FDB4913FF345AE90CD14BE3607",
        "St": 3,
        "PCS": 2,
        "ConnId": null,
        "DN": "Roberto Burton",
        "RB": 2,
        "LL": 1,
        "Chips": 5000,
        "Rank": 46,
        "ExitLvl": 0,
        "TRA": 0,
        "PRI": "",
        "IsAns": false
      },
      {
        "_id": 1049005,
        "UserId": "FC378E3C-C2A0528F094A-7FB31BD9D278",
        "St": 3,
        "PCS": 2,
        "ConnId": null,
        "DN": "Summer Stephenson",
        "RB": 2,
        "LL": 1,
        "Chips": 5000,
        "Rank": 46,
        "ExitLvl": 0,
        "TRA": 0,
        "PRI": "",
        "IsAns": false
      },
      {
        "_id": 1049006,
        "UserId": "FC431BBB-A79D45A0EAB034676C755E5A",
        "St": 3,
        "PCS": 2,
        "ConnId": null,
        "DN": "Terence Fischer",
        "RB": 2,
        "LL": 1,
        "Chips": 5000,
        "Rank": 46,
        "ExitLvl": 0,
        "TRA": 0,
        "PRI": "",
        "IsAns": false
      }
    ]
  }

Out Put : For example , Count: 5

Answer 1

Not all MongoDB query features are surfaced in the Cosmos MongoDB API, and currently the Aggregation Pipeline is one of those features not implemented. This is why you're getting the error stating $group isn't supported.

EDIT - As of November 2017, Aggregation Pipeline is now a supported operation with the MongoDB API of Cosmos DB. Full list of supported features is here.

General aggregation (sum, min, max, avg, count) were added a while back to the DocumentDB API.

You'd have to do your own aggregation in MongoDB API, for now.

Note: count() is supported, so assuming you filter your data (without aggregation pipeline), you can do something like:

db.collection.find({...}).count()

Likewise, you can do a $min or $max via sorting. So for $max, you can do something like this to find the maximum value of foo:

db.collection.find({...},{foo:1}).sort({foo:-1}).limit(1)

Answer 2

Aggregation pipeline support for Cosmos DB is in private preview. Please feel free to reach out to the team or me to get it enabled for your database.