CODEWITHSUNDEEP
c#mongodbmongodb-queryaggregation-frameworkmongodb-.net-driver
AlexKogan
AlexKogan

Reputation: 355

select specific field in nested array in mongodb using c#

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!

Upvotes: 0

Views: 3030

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151220

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

Upvotes: 1

Related Questions