MongoDb regex search in array objects

Question

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"?

Yong Shun · Accepted Answer · 2021-11-04 09:13:17Z

1

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

answered Nov 4, 2021 at 9:13

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

MongoDb regex search in array objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related