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

By the way: You aren't moving to NoSQL. You are moving to MongoDB. All the different databases commonly grouped under the catch-all label "NoSQL" have nothing in common except that they don't use SQL as a query language. Instead each one uses an own query language and each of these languages is as different from the others as they are different from SQL. The step from MongoDB to, say, Redis or Neo4j is as big a step as the step from SQL to MongoDB. — Philipp
– Philipp, Commented Mar 5, 2015 at 12:12

RubioRic · Accepted Answer · 2023-03-20 22:15:10Z

44

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.

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

This pipeline would look like this:

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

edited Mar 20, 2023 at 22:15

RubioRic

2,4684 gold badges32 silver badges38 bronze badges

answered Mar 5, 2015 at 12:09

Philipp

70.1k10 gold badges121 silver badges159 bronze badges

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

1 Comment

Gratis Devs Over a year ago

Hey, this query would return only the first matched object in array, not all items

Collectives™ on Stack Overflow

MongoDB - FindOne and filter a nested array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related