MongoDB - FindOne and filter a nested array

Question

I am moving from SQL over to MonboDB, and I am trying learn the basics. But so far I have struggled with one "Select statement"

Lets say I have this kind of collection

document 1

{
  "_id" : "id1",
  "name" : "aa",
  "array"  : [
       {
          "nested_id"  : "n323123",
          "nesteddata" : "lorem",
          "active"     : 1
       },
       {
          "nested_id"  : "n353123",
          "nesteddata" : "lorem",
          "active"     : 0
       },
       {
          "nested_id"  : "n323123",
          "nesteddata" : "lorem",
          "active"     : 1
       }   
   ] 
}

document 2

{
      "_id" : "id2",
      "name" : "bb",
      "array"  : [
           {
              "nested_id"  : "n325123",
              "nesteddata" : "lorem",
              "active"     : 1
           },
           {
              "nested_id"  : "n355123",
              "nesteddata" : "lorem",
              "active"     : 1
           },
           {
              "nested_id"  : "n323123",
              "nesteddata" : "lorem",
              "active"     : 0
           }   
       ] 
 }

I then want to select one and ONLY the nested data. Have tried with the following statement without any success.

db.testing.findOne(  {_id: "id1", "array.active" : 1} )

but I am still getting all the data without the filter

Answer 1

First, when you want to get only part of a document, you need to use the two-object form of find:

db.testing.findOne(  
    { _id: "id1", "array.active" : 1 },
    { array: 1 }
);

The second object tells you which fields of the document to return. However, this query will return the whole array even when there is only one matching entry. But you want only a specific array entry. So you have to use the positional-operator $ in your second object. The "$" is a placeholder for the array-entry which were matched by the first object.

db.testing.findOne(  
    { _id: "id1", "array.active" : 1 },
    { "array.$": 1 }
);

There is still a restriction which might or might not be problematic for your use-case: The $-operator only returns the first entry of each array per document. You are using findOne so I assume your intention is to get only one array entry anyway. But this restriction might bite you in the future.

When you want to get all the array entries, you will have to use an aggregation pipeline with 3 steps.

1. filter the document(s) you need results from with $match
2. unwind the arrays to streams of individual documents with $unwind
3. filter that resulting document-stream again by the condition which applies to array attributes with another $match
4. (optional) reduce the documents to only those fields you want with $project

This pipeline would look like this:

db.testing.aggegate([
     { $match: { _id: "id1" } },
     { $unwind: { array: 1 } },
     { $match: { "array.active": 1 } },
     { $project: { _id: 0, array: 1 } 
 ]);