How to filter query in mongoDB in collection

Question

I am trying to do filter in mongoDB, but I am not getting the expected output. I am using $elemMatch but still not getting expected out.

Agency Collection

[

  {
    "_id": "5d402d36035f3063657240ac",
    "name": "test agency",
    "msisdn": "99961110278",
    "apps": [
      {
        "pincodes": [
          "135001",
          "122018"
        ],
        "_id": "5d402d36035f3063657240ae",
        "name": "DSB"
      },
      {
        "pincodes": [
          "135001",
          "122017"
        ],
        "_id": "5d402d36035f3063657240ad",
        "name": "DBH"
      }
    ],
    "__v": 0
  }
]

query on shell

db.Agency.find({'apps':{$elemMatch:{name: {$regex: 'DSB', $options: 'i'} ,pincodes: { $in : ['135001','134002']}}}})

getting output

[
{
  "_id" : "5d401d4aba592260507479d2",
  "name" : "test agency",
  "msisdn" : "99961110278",
  "apps" : [
    {
      "pincodes" : [
        "135001",
        "122017"
      ],
      "_id" : "5d401d4aba592260507479d3",
      "name" : "DSB"
    }
  ],
  "__v" : 0
}
,
{
  "_id" : "5d402249035f3063657240a9",
  "name" : "test agency",
  "msisdn" : "99961110278",
  "apps" : [
    {
      "pincodes" : [
        "135001",
        "122017"
      ],
      "_id" : "5d402249035f3063657240ab",
      "name" : "DSB"
    },
    {
      "pincodes" : [
        "135001",
        "122017"
      ],
      "_id" : "5d402249035f3063657240aa",
      "name" : "DBH"
    }
  ],
  "__v" : 0
}
,
{
  "_id" : "5d402d36035f3063657240ac",
  "name" : "test agency",
  "msisdn" : "99961110278",
  "apps" : [
    {
      "pincodes" : [
        "135001",
        "122018"
      ],
      "_id" : "5d402d36035f3063657240ae",
      "name" : "DSB"
    },
    {
      "pincodes" : [
        "135001",
        "122017"
      ],
      "_id" : "5d402d36035f3063657240ad",
      "name" : "DBH"
    }
  ],
  "__v" : 0
}]

expected output

[
{
  "_id" : "5d401d4aba592260507479d2",
  "name" : "test agency",
  "msisdn" : "99961110278",
  "apps" : [
    {
      "pincodes" : [
        "135001",
        "122017"
      ],
      "_id" : "5d401d4aba592260507479d3",
      "name" : "DSB"
    }
  ],
  "__v" : 0
}
,
{
  "_id" : "5d402249035f3063657240a9",
  "name" : "test agency",
  "msisdn" : "99961110278",
  "apps" : [
    {
      "pincodes" : [
        "135001",
        "122017"
      ],
      "_id" : "5d402249035f3063657240ab",
      "name" : "DSB"
    }
  ],
  "__v" : 0
}
,
{
  "_id" : "5d402d36035f3063657240ac",
  "name" : "test agency",
  "msisdn" : "99961110278",
  "apps" : [
    {
      "pincodes" : [
        "135001",
        "122018"
      ],
      "_id" : "5d402d36035f3063657240ae",
      "name" : "DSB"
    }
  ],
  "__v" : 0
}]

score 1 · Answer 1 · answered Jul 30 '19 at 14:09

You can do in another way too by $group and $match.

db.getCollection('test').aggregate([
  {$match: {"apps.name": {$regex: 'DSB', $options: 'i'}}}, // Return only data which name is DSB
  {$unwind: "$apps"}, // Convert array to object (To remove DTH data in same document)
  {$match: {"apps.name": {$regex: 'DSB', $options: 'i'}, // Again filter with DSB and pincodes "apps.pincodes": { $in : ['135001','134002']}}},
  {$group: { // Finall group it again. So we will have only DSB related data
    _id: "$_id",
    name: {$first: "$name"},
    msisdn: {$first: "$msisdn"},
    __v: {$first: "$__v"},
    apps: {$push: "$apps"}
  }}
])

score 0 · Accepted Answer · answered Jul 30 '19 at 13:52

0

You may need $filter to do this.

db.Agency.aggregate([
  {
    $project: {
      apps: {
        $filter: {
           input: "$apps",
           as: "app",
           cond: {"$$app.name": {$regex: 'DSB', $options: 'i'} ,"$$app.pincodes": { $in : ['135001','134002']}}
        }
     }
    }
  }
])

answered Jul 30 '19 at 13:52

Cuong Le Ngoc

11,595
2
17
39

could you please explain query ..!! little bit – user944513 Jul 30 '19 at 14:09
Refer to the doc, this query "Selects a subset of an array to return based on the specified condition", in this case the condition is `{"$$app.name": {$regex: 'DSB', $options: 'i'} ,"$$app.pincodes": { $in : ['135001','134002']}}`. `input` is the `apps` field and `as` "represents each individual element of the input array". – Cuong Le Ngoc Jul 30 '19 at 14:16

score 0 · Answer 3 · answered Jul 30 '19 at 14:36

MongoDB would return the complete array if any of the element in the array matches the condition. If you just need the matched element, you would need to use aggregation on data. The following query can get you the expected output:

db.agency.aggregate([
    {
        $unwind:"$apps"
    },
    {
        $match:{
            "apps.name":{
                $regex: "DSB", 
                $options: 'i'
            },
            "apps.pincodes": { 
                $in : ['135001','134002']
            }
        }
    },
    {
        $group:{
            "_id":"$_id",
            "name":{
                $first:"$name"
            },
            "msisdn":{
                $first:"$msisdn"
            },
            "apps":{
                $push:"$apps"
            },
            "__v":{
                $first:"$__v"
            }
        }
    }
]).pretty()

Stage I: Opens up the array and creates individual documents for each array element
Stage II: Applying condition
Stage III: Grouping information again on the basis of _id

How to filter query in mongoDB in collection

3 Answers3