MongoDB query an array of documents for specific match-CodePudding

I want to search the transactions array looking for a specific match. In this example, by pipedrive_id.

This is what I tried (as per mongodb instructions and this other stack overflow post)

const pipedrive_id = 1677;
const inner_pipedrive_id = 1838;

const result = await Transactions.find({
    pipedrive_id,
    'transactions': { $elemMatch: { 'pipedrive_id': inner_pipedrive_id } }
});

const result2= await Transactions.find({
    'transactions': { $elemMatch: { 'pipedrive_id': inner_pipedrive_id } }
});

const result3 = await Transactions.find({
    'transactions.pipedrive_id': inner_pipedrive_id
});

And each result itteration returns all transaction items (all 6 items, instead of 2 [that's how many Mark Smith has in the array).

CodePudding user response：

As the doc, $elemMatch matches documents that contain an array field with at least one element that matches the criteria. To filter the result inside the array, you will need to use $filter from aggregation Ref: https://www.mongodb.com/docs/manual/reference/operator/aggregation/filter/

CodePudding user response：

You can use aggregate to filter out the array. Something like this

You can remove $project if you want all the fields

db.collection.aggregate([
  {
    $match: {
      pipedrive_id: "1677"
    }
  },
  {
    $unwind: "$transactions"
  },
  {
    $match: {
      "transactions.pipedrive_id": "1838"
    }
  },
  {
    $project: {
      _id: 0,
      pipedrive_id: 1,
      transactions: 1
    }
  }
])

You can check the Mongo playground here.