MongoDb regex search in array objects-CodePudding

I have the following collection:

{
    "invoice": {
        "data": [{
                "name": "VOUCHERNUMBER",
                "value": "59302311"
            }, {
                "name": "VOUCHERDATE",
                "value": "2020-02-20"
            }
        ]
    }
},
{
    "invoice": {
        "data": [{
                "name": "VOUCHERNUMBER",
                "value": "59112389"
            }, {
                "name": "VOUCHERDATE",
                "value": "2020-02-20"
            }
        ]
    }
},
{
    "invoice": {
        "data": [{
                "name": "VOUCHERNUMBER",
                "value": "59302378"
            }, {
                "name": "VOUCHERDATE",
                "value": "2020-02-11"
            }
        ]
    }
}

My task is to build a query that find all invoices which invoicenumbers includes "11" (or any other substring).

So I built the following statement:
{"invoice.data.name": "VOUCHERNUMBER", "invoice.data.value": {$regex : "11"} } I'm expecting a result of the first two objects, but because of the second value in the third object, mongodb returns me all three objects. Then I tried
{$and : [{"invoice.data.name": "VOUCHERNUMBER"}, {"invoice.data.value": {$regex : "11"}}]}
with the same result ...

So I'm running out of ideas. Is there a solution to search for the string only in the value field where the corresponding "name" field contains "VOUCHERNUMBER"?

CodePudding user response：

You need $elemMatch.

The $elemMatch operator matches documents that contain an array field with at least one element that matches all the specified query criteria.

db.collection.find({
  "invoice.data": {
    "$elemMatch": {
      "name": "VOUCHERNUMBER",
      "value": {
        $regex: "11"
      }
    }
  }
})

Sample Mongo Playground