(C#, driver 2.0) Selecting/projecting child collections

[Kieren Johnstone]

I'm trying to achieve this kind of query:

class SubClass

{

    string Test { get; set; }

    string Other { get; set; }

}

class MyClass

{

    string Id { get; set; }

    SubClass[] SubItems { get; set; }

}

IMongoCollection<MyClass> c;

var results = c

    .Where(x => x.Id == "something")

    .Select(x => x.SubItems.Select(s => new { Test = s.Test }));

Basically, just to pull out a projection which includes (or is entirely based on) some sub-items of the collection type.

But the C# driver generates an invalid query for Mongo, using $push in $project I think:

A first chance exception of type 'MongoDB.Driver.MongoCommandException' occurred in MongoDB.Driver.Core.dll

Additional information: Command aggregate failed: exception: invalid operator '$push'.

The command looks like this (the types are different but the structure is the same as above):

{ "aggregate" : "User", "pipeline" : [{ "$match" : { } }, { "$match" : { "_id" : "fcc3e483cfdc4676b6fadc092d854a5e" } }, { "$skip" : 0 }, { "$limit" : 100 }, { "$project" : { "__fld0" : { "$push" : { "AppName" : "$AppIntegrations.AppName" } }, "_id" : 0 } }], "cursor" : { } }

How should/can I go about doing this, please?  With the driver ideally, but if not I can build a custom BsonDocument...

Thanks

Kieren

[Craig Wilson]

Hi Kieren,

  There seems to be a disconnect here. Could you provide the actual query you are doing, because the LINQ style query you posted above isn't possible right now and also doesn't match the pipeline you provided. I need to know exactly how $push is getting generated because it isn't legal in a $project, only in a $group.

Craig

[Kieren Johnstone]

Hi Craig, sure.  The I missed the AsQueryable().  I've flattened things here but included everything called on/passed to the driver:

    // mongo type for User

    public class User

    {

        public User()

        {

            AppIntegrations = new UserAppIntegration[] { };

        }

        public string Id { get; set; }

        public string Email { get; set; }

        public UserAppIntegration[] AppIntegrations { get; set; }

    }

    public class UserAppIntegration

    {

        public string AppName { get; set; }

        public string AppUserId { get; set; }

        public bool UsedForLogin { get; set; }

        public string AccessToken { get; set; }

        public DateTime? TokenExpiry { get; set; }

    }

    // type used for projection - array of these to be returned by service

    public class UserAppIntegrationStatus

    {

        public string AppName { get; set; }

        // todo : other fields here...

    }

            IMongoCollection<User> _collection = /* get collection */;

            Expression<Func<User, bool>> filter = u => u.Id == "user_id_parameter_here";

            Expression<Func<User, UserAppIntegrationStatus>> projection = u => u.AppIntegrations.Select(i => new UserAppIntegrationStatus { AppName = i.AppName });

            int skip = 0;

            int take = 0;

            // prepare query and count

            var query = _collection.AsQueryable().OfType<User>();

            if (filter != null)

            {

                query = query.Where(filter);

            }

            // slice, project and execute

            query = query.Skip(skip);

            query = query.Take(take);

            var projected = query.Select(projection);

            var results = projected.ToArray();

            return results;

This is my fork, a little behind the master now: https://github.com/kierenj/mongo-csharp-driver

(See also my workaround commit there to get generic types working correctly.  It's the non-backwards-compatible patch)

In fact I've tried a few ways to get this kind of projection to work.  I'm leveraging the LINQyness, which works excellently of course, but am happy to take a more difficult route if needed.

Thanks Craig,

Kieren

[Craig Wilson]

Ok. That clears it up. You are working on a custom build behind master.

Yeah, Select shouldn't be turning that nested Select method into a $pull, but it is. I've filed https://jira.mongodb.org/browse/CSHARP-1301 so I don't forget to fix it. I"m not sure how to get around that right now.

Couple other things: You have that empty $match statement because you are doing an OfType to the same type as the collection. Also, it would me much better to use the async extensions to IMongoQueryable. So, ToListAsync() instead of ToArray(). Otherwise, you are running sync over async.

Craig

[Kieren Johnstone]

Thanks :). Is there another way to get the results I want with the c# driver?

Kieren

[Craig Wilson]

If you don't use LINQ, you can just use the Aggregate() method off of IMongoCollection<T>. It will look a lot like LINQ, so not much would need to change.

[Kieren Johnstone]

I dropped my same expressions into Project(), Filter() etc and got a fresh exception around deserialising:

A first chance exception of type 'System.FormatException' occurred in MongoDB.Bson.dll

Additional information: Cannot deserialize a 'List<UserAppIntegrationStatus>' from BsonType 'Document'.

Although the query looks good, as below.  (I was also able to do some more in depth logic, where my CanRemove is !UsedForLogin)

{ "aggregate" : "User", "pipeline" : [{ "$match" : { "_id" : "5aa45dd874d44139b732ec772da651d4" } }, { "$project" : { "__fld0" : { "$map" : { "input" : "$AppIntegrations", "as" : "i", "in" : { "AppName" : "$$i.AppName", "CanRemove" : { "$not" : "$$i.UsedForLogin" } } } }, "_id" : 0 } }, { "$skip" : 0 }, { "$limit" : 100 }], "cursor" : { } }

The exception is thrown in EnumerableSerializerBase.Deserialize, because serializer.IsPositionedAtDiscriminatedWrapper(context) returns false.

Logically I would guess that's true - that part of the document isn't a wrapper with a discriminator field.

Is this a separate, also valid, bug?

Thanks again
Kieren

[Craig Wilson]

Maybe... The problem seems to be that AppIntegrations is coming back as a document where we expect an array. I'll need to try and reproduce this... I'll get back to you tomorrow...

[Kieren Johnstone]

Hi Craig,

Shall I put something into JIRA to keep this alive perhaps?

Kieren

[Craig Wilson]

Hi Kieren,

I've reproduced this. You can work around it now by projecting into an anonymous type (or a nominal type if you want to create one).

var pipeline = col.Aggregate()
                .Match(u => u.Id == "1")
                .Project(u => new { SomeField = u.AppIntegrations.Select(i => new UserAppIntegrationStatus { AppName = i.AppName, CanRemove = !i.CanRemove }) });

I've filed a ticket here: https://jira.mongodb.org/browse/CSHARP-1306. I also filed CSHARP-1301 about the LINQ problem with $pull: https://jira.mongodb.org/browse/CSHARP-1301.

Thanks for the report,

Craig

[Kieren Johnstone]

Hi Craig,

Another related issue here - hope you don't mind me posting these.

I'm trying to get an $elemMatch into an aggregate pipeline w/LINQ.  Basically I'm trying to filter an array element but it just seems to be not supported.

Looking at AggregateLanguageTranslator.BuildMethodCall, ExtensionExpressionVisitor.IsLinqMethod(node) returns true for the 'Where', but TryBuildLinqMethodCall fails because it doesn't recognise 'Where'.

From checking the Mongo docs, it looks like an $elemMatch here should be possible.  But it looks like once you're in the QueryableExecutionModelBuilder.VisitSelect(), there's no chance to get into PredicateTranslator to deal with any Where()s in the expression..?

Would it be bad form to delegate back out to a new PredicateTranslator here - I could attempt to put together a PR if it's helpful?

Thanks again

Kieren

[Craig Wilson]

To get $elemMatch, you should be using Any, not Where. Where filters out certain documents, but Any get's you a true/false for a match.

This page (http://mongodb.github.io/mongo-csharp-driver/2.0/reference/driver/expressions/) provides a comprehensive list of translations for the aggregation framework currently implemented in 2.0.1.

[Kieren Johnstone]

I'm looking to use $elemMatch in the projection, not the filter - to get only elements of an array which match a predicate, within the set of returned documents (each of which matched a separate predicate).

So for example:  _ordersCollection.Where(o => o.AccountId == "ABC").Select(o => new { orderRef = o.Ref, outOfStockLines = o.Lines.Where(l => !l.IsInStock) });

I.e., get all orders for account ID, but only return the lines which aren't in stock.

Unless I'm misunderstanding, I think this means this simply isn't implemented currently?  I'm giving it a go at the moment on my fork and am making some progress..

Kieren

[Kieren Johnstone]

Nevermind, this isn't supported yet on the server, I misunderstood and combined that with the idea that anything supported by a find() projection would be supported in an aggregation $project.  Just found this comment: 

https://jira.mongodb.org/browse/SERVER-14876?focusedCommentId=919480&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-919480

It's a shame, since I just managed to put together some unit tests and modify everything to get as far as this (though probably still not correct, even if it was possible), I was quite proud:

{ "aggregate" : "testcollection", "pipeline" : [{ "$project" : { "Result" : { "$elemMatch" : { "G.E.F" : 33 } }, "_id" : 0 } }], "cursor" : { } }

I guess then IMongoCollection.AsQueryable() always uses the aggregation framework - no way to do LINQ with standard Find()s etc or any other workarounds for this one?

Kieren

[Kieren Johnstone]

In fact it looks like $filter made it in to either 3.1.3 or 3.1.4 - https://github.com/mongodb/mongo/commit/6b38c7a53f2e284583199c12b4b9f6cd8d69004a

Afraid I'm still getting up to speed with the dev process for Mongo and the various drivers.  With something like this, what's the typical expected lead time?  Not being pushy at all, just curious, and am happy to help implement if I can!

Kieren