72778

Azure CosmosDB using Mongo Drivers: Get Count With out getting all documents based on element in sub

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

<ol> <li>

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

</li> <li>

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 <strong>tbls</strong> ) is there any way that I can query on <strong>Plyrs</strong>(sub sub collection) .

</li> <li>

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

</li> </ol>

Please guide me <strong>Aggregation</strong>

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) } });

<strong>Builders</strong>

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

<strong>Error</strong> : 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 } ] }

<strong>Out Put</strong> : For example , Count: 5

Answer1:

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.

<strong>EDIT</strong> - 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)

Answer2:

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.

Recommend

  • cosmos db api for mongo db facing issue for some commands
  • Setting last modification timestamp when using insertOne and findOneAndUpdate
  • How to get array of document using Mongodb java?
  • Disabling checkbox in “multiselect:true” mode for a specific row in jqgrid
  • Automated Apps Script for BigQuery Failing
  • Highlight country around the border from your search area only
  • Create row with java script but its gone on my asp panel
  • WorkManager Data.Builder does not support Parcelable
  • MySQL - updating all records to match max value in group
  • find in array between dates in mongo
  • Insert array where element does not exist else update it ( with multiple conditions )
  • Azure Cosmos DB check if array in field is contained in search array
  • Locating unmatched delimiters in Clojurescript
  • How to grep for a specific pattern and print everything above and below that pattern until you reach
  • C# COM Component Fails To Read Config When Loaded Into An Unmanaged C++ App
  • How do I configure Maven Cargo to use an embedded Tomcat server?
  • Select column where another related column's total is 0
  • Cryptic error when trying to run POW
  • Passing parameter through “window.location.href”
  • Jenkins: could not create Android emulator failed to parse AVD config file
  • Unable to run tests with JUnit5 Console Launcher
  • Let a function return any type in C++ class
  • Counting problem C#
  • 550 Access denied - Invalid HELO name
  • pillow imaging ImportError
  • Check for zero lines output from command over SSH
  • Redux Form - Not able to type anything in input
  • Problems installing Yesod for Haskell
  • Unable to install Git-core+svn by MacPorts
  • VSO Build — Response status code does not indicate success: 404 (Not Found)
  • dc-js disable selecting slices on click for pie chart
  • MongoDB in PHP using aggregate to group by _id is null not working
  • ilmerge with a PFX file
  • Spring security and special characters
  • retrieve vertices with no linked edge in arangodb
  • Linking SubReports Without LinkChild/LinkMaster
  • python draw pie shapes with colour filled
  • Is there any way to bind data to data.frame by some index?
  • How can i traverse a binary tree from right to left in java?
  • Python/Django TangoWithDjango Models and Databases