how to use $filter in nested array in Mongo db?

Question

I have following documents in Agriculture model

{
"_id" : ObjectId("5e5c9c0a0cfcdb1538406000"),
"agricultureProductSalesType" : [ 
    "cash_crops", 
    "vegetable", 
    "fruit"
],
"flag" : false,
"agricultureDetail" : [ 
    {
        "production" : {
            "plantCount" : 0,
            "kg" : 0,
            "muri" : 0,
            "pathi" : 0
        },
        "sale" : {
            "plantCount" : 0,
            "kg" : 0,
            "muri" : 0,
            "pathi" : 0
        },
        "_id" : ObjectId("5e5c9c0a0cfcdb1538406001"),
        "title" : "alaichi",
        "agricultureProductionSalesType" : "cash_crops"
    }, 
    {
        "production" : {
            "plantCount" : 0,
            "kg" : 40,
            "muri" : 0,
            "pathi" : 0
        },
        "sale" : {
            "plantCount" : 0,
            "kg" : 0,
            "muri" : 0,
            "pathi" : 0
        },
        "_id" : ObjectId("5e5c9c0a0cfcdb1538406002"),
        "title" : "amriso",
        "agricultureProductionSalesType" : "cash_crops"
    }
],
"agricultureParent" : [ 
    {
        "area" : {
            "ropani" : 10,
            "aana" : 0,
            "paisa" : 0
        },
        "_id" : ObjectId("5e5c9c0a0cfcdb1538406005"),
        "title" : "cash_crops",
        "income" : 50000,
        "expense" : 6000
    }
],
"house" : ObjectId("5e5c9c090cfcdb1538405fa9"),
"agricultureProductSales" : true,
"insecticides" : false,
"fertilizerUse" : true,
"seedNeed" : "local",
"__v" : 0

I want result from above document with condition if agricultureDetail.title is not empty or blank string AND agricultureDetail.production.plantcount equals zero or null or exists false AND agricultureDetail.production.kg equals zero or null or exists false AND (all remaining elements inside production zero or null or exists flase).

I tried $elemMatch as bellow:

 $and: [
      { agricultureProductSales: true },
      { agricultureDetail: { $exists: true, $ne: [] } },
      {
        $or: [
          { agricultureDetail: { $elemMatch: { title: { $ne: "" } } } },
          {
            agricultureDetail: { $elemMatch: { title: { $exists: true } } }
          }
        ]
      },
      {
        $or: [
          {
            agricultureDetail: {
              $elemMatch: { production: { $exists: true } }
            }
          },
          {
            agricultureDetail: {
              $elemMatch: { production: { $ne: [] } }
            }
          }
        ]
      },
      {
        $or: [
          {
            "agricultureDetail.production": {
              $elemMatch: { plantCount: { $exists: false } }
            }
          },
          {
            "agricultureDetail.production": {
              $elemMatch: { plantCount: { $eq: 0 } }
            }
          },
          {
            "agricultureDetail.production": {
              $elemMatch: { plantCount: { $eq: null } }
            }
          }
        ]
      }
    ]

But it reurns empty result. Any help? THankyou so much.

$elemMatch is not necessary if you are only testing 1 field. i.e. {"agricultureDetail.production": { $elemMatch: { plantCount: { $eq: null } } } is equivalent to {"agricultureDetail.production.plantCount": { $eq: null } } — Joe
– Joe, Commented Mar 18, 2020 at 21:30
The $or matching $exists:true or $ne:[] will always match. $ne:[] matches documents for which the field does not exist, so that is essentially testing {$exists:true} or {$exists:false} likewise with the $ne: "" or $exists: true — Joe
– Joe, Commented Mar 18, 2020 at 21:33

Joe · Accepted Answer · 2020-03-18 21:59:47Z

1

Breaking down this query a bit:

{ agricultureProductSales: true },

Selects only true values.

{ agricultureDetail: { $exists: true, $ne: [] } }

Is extraneous. Since you are testing fields of sub-documents within this array, those later tests could not possibly succeed if the array were empty or didn't exist.

 {
        $or: [
          { agricultureDetail: { $elemMatch: { title: { $ne: "" } } } },
          {
            agricultureDetail: { $elemMatch: { title: { $exists: true } } }
          }
        ]
      },

This tests to see if title either doesn't equal "" (which includes elements where the field doesn't exist) or if the title field exists. One of these is always true, so this $or will always match. If you wanted to match only documents that contain an element with a non-empty title, test to see if it is greater than "" - since the query operators are type-sensitive, this will fail to match any title that doesn't exist, doesn't contain a string, or contains the empty string.

  "agricultureDetail.title": { $gt: "" }

Similarly with plantCount, if you were to test $gt: 0, that would match only documents that contain a plantCount that is numeric and greater than 0. What you want is the logical inverse of that, so:

  "agricultureDetail.production.plantCount": {$not: {$gt: 0}}

In this case, that would match elements that do not contain a production field, or those that have an empty array for the production field.

An existence test for plantCount will eliminate both of those possibilities, so

  "agricultureDetail.production.plantCount": {$exists:true, $not: {$gt: 0}}

As written, all of these are testing if any element in the array matches any of the criteria.

If your intent is to match a document that contains a single element that matches all of the criteria, you would collect them together in an $elemMatch of the agricultureDetail fields. So the final query could look something like:

db.collection.find({
    agricultureProductSales: true,
    agricultureDetail:{$elemMatch:{
          title: {$gt: ""},
          "production.plantCount": {$exists:true, $not: {$gt: 0}}
    }}
})

Playground

answered Mar 18, 2020 at 21:59

Joe

29k4 gold badges25 silver badges50 bronze badges

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

2 Comments

Silen Over a year ago

Thankyou so much @Joe. It works fine. And thanks for each and every line explanation of query. It means a lot for me who is new to mongo db. I want to ask additional Joe.Can i add following code in above query inside $elematch "production.plantCount": { $exists: true, $not: { $gt: 0 } }, "production.kg": { $exists: true, $not: { $gt: 0 } }, "production.muri": { $exists: true, $not: { $gt: 0 } }, "production.pathi": { $exists: true, $not: { $gt: 0 } }

Joe Over a year ago

As long as production will be an object, yes you can do that. If the production field contains an array that would match more than you intend.

Collectives™ on Stack Overflow

how to use $filter in nested array in Mongo db?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related