select specific field in nested array in mongodb using c#

Question

My application accesses a mongodb using the .net c# driver.

My data structure looks like this:

{
 "_id" : ObjectId("53d97351e37f520a342e152a"),
  "Name" : "full question test 2",
  "keywords" : ["personality", "extraversion", "agreeableness"],
   "Questions" : [{
     "type" : "likert",
      "text" : "question 1",
       }, 
       {
      "type" : "likert",
      "text" : "question 2",
      }]
}

What I want to do is to select only the type column from the questions array.

Here is my linq code now:

from e in collection.AsQueryable<scales>() 
where e.Name == "full question test 2"
select new { e.Id, e.Name, e.Questions}

This returns all the question properties (both type and text). I want just the type. I can do this by saying something like e.Questions[0].text,e.Questions[1].text.

But the number of questions varies from file to file, so I'd love a solution that doesnt require this manual coding.

Open to ideas!

Neil Lunn · Accepted Answer · 2014-07-31 01:01:30Z

1

The standard query methods that are wrapped here have a form of "projection" available for field selection, but this is however not capable of doing things such as selecting specific fields within an array element. At least for multiple fields anyway.

But single fields should be possible just using the dot notation form to access the element:

from e in collection.AsQueryable<scales>() 
where e.Name == "full question test 2"
select new { e.Id, e.Name, e.Questions.type }

In order to do anything more you need the form of projection that is available to the aggregation framework where your "query" and "projection" are represented as BSON documents using the $match and $project operators for the pipeline. In the shell form it looks like this:

db.collection.aggregate([
    { "$match": {
        "Name": "fullquestion test 2"
    }},
    { "$project": {
        "Name": 1,
        "Questions.type": 1
    }}
])

Or with constructing the BSON documents for C#:

var match = new BsonDocument {
    { "$match",new BsonDocument {
           {
                "Name", "full question test 2"
           }
      }
    }
};

var project = new BsonDocument {
    { "$project", new BsonDocument {
          { "Name", 1 },
          { "Questions.type": 1 }         
      }
    }
};

var pipeline = new [] { match, project };
var result = collection.aggregate(pipeline);

Essentially, the $project stage of the aggregation pipeline can do a lot more things than just select fields. The additional support here allows for things such as "changing" the structure of the document inside the array.

Support for aggregation pipeline mapping to Linq is a work in progress. You can monitor the issues here: CSHARP-601

edited Jul 31, 2014 at 1:01

answered Jul 31, 2014 at 0:26

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Justin Case Over a year ago

Are you sure w.r.t. standard query methods not being to do this?db.collection.find({"Name": "fullquestion test 2"}, {"Name": 1, "Questions.type": 1}) returns the same results. Or is it a C# thing?

Neil Lunn Over a year ago

@JustinCase Brain on autopilot. Single field, so you are right and a standard projection should work for a single field. Still thinking in multiple field terms.

AlexKogan Over a year ago

Thanks for the quick response! Unfortunately the dot notation (id.Questions.type) doesnt work :( I get an error that says "System.Array does not contain definition of 'type'"

Neil Lunn Over a year ago

@AlexKogan Wasn't sure if that worked in projection context with linq, and have not had the time to fire up. But the aggregation method does work. It just means that you cannot form a query with linq for this.

Collectives™ on Stack Overflow

select specific field in nested array in mongodb using c#

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related