MongoDB - Conditionally match two arrays

Question

There is the following array of objects:

let externalArray = 
[
 { name: "xxx",
   max: 100,
   unit: "myUnit"
 },
 { name: "yyy",
   max: 90,
   unit: "myUnit"
 }
]

And the following mongodb structure:

[
    {
        "myList": [
            {
                "name": "xxx",
                "amount": 66.3,
                "unit": "myUnit"
            },
            {
                "name": "yyy",
                "amount": 11.6,
                "unit": "myUnit"
            },
            {
                "name": "zzz",
                "amount": 6.9,
                "unit": "myUnit"
            }
        ]
    }
]

How can I use the $match query inside an aggregation to only output the objects for which myList.amount <= externalArray.max and myList.unit equals externalArray.unit for the same name? Thank you so much for your help.

Unfortunately no... as I am trying to dynamically use values from an external array... — colinrussl, Jun 05 '20 at 19:26

score 1 · Accepted Answer · answered Jun 05 '20 at 20:01

Since you're comparing against input array to an array in documents you need to do few more things along with $filter :

db.collection.aggregate([
    /** Unwind (Split array into objects :: total docs = each doc * no.of objects in array of respective doc) */
    {
      $unwind: "$myList"
    },
    /** Iterate on input `externalArray` & check for all conditions. Creates an array field `matches` */
    {
      $addFields: {
        matches: {
          $filter: {
            input: externalArray,
            cond: {
              $and: [ { $eq: [ "$$this.name", "$myList.name" ] },
                { $eq: [ "$$this.unit", "$myList.unit" ] },
                { $lte: [ "$myList.amount", "$$this.max" ] }
              ]
            }
          }
        }
      }
    },
    /** In filter step if it matches with any condition then `matches` will have 1 or more objects from `externalArray` 
     * Excluding all docs where there is no match */
    {
      $match: { matches: { $ne: [] } }
    },
    /** Remove unnecessary field */
    {
      $project: { matches: 0 }
    },
    /** Since we unwind the array - group back all docs based on `_id` */
    {
      $group: { _id: "$_id", myList: { $push: "$myList" } }
    }
  ])

Test : mongoplayground

Note :

In the response :

myList array will contain only matched objects from externalArray.
You don't see documents where myList is not present or if no matching object exists between myList & externalArray (So there should be at-least one matching object between two arrays in order to get the document out).

This makes a lot of sense, thank you so much! It definitely solves my initial question and I am going to mark your answer as correct, but I've just realised I have two other lists like "myList" in my db structure. Shall I unwind all of them and go through the same steps? But how am I going to group everything at the end? Just with _id: "$_id" it seems not to work. — colinrussl, Jun 05 '20 at 20:48
@andfrst : Oh ok, Often arrays & nested arrays will always be an issue not just in MongoDB but in programming languages as well :-) Even this query is a bit complex if used on huge datasets(try to filter docs in initial stages) ! Unless we see the data we can't say anything.. Maybe you can raise a new question with sample data(entire doc) & required o/p - someone can help you with a query.. — whoami - fakeFaceTrueSoul, Jun 05 '20 at 20:52

thammada.ts · Answer 2 · 2020-06-05T21:38:15.060

You can transform your external array before passing it to aggregation pipeline to be the following structures and use as conditions.

We can use this in a $match expression to match the documents that has at least one match.

[{
  "name": "xxx",
  "amount": {
    "$lte": 100
  },
  "unit": "myUnit"
},
{
  "name": "yyy",
  "amount": {
    "$lte": 90
  },
  "unit": "myUnit"
}]

/* example js implementation */
const matchConditions = externalArray.map(({ name, max, unit })=> ({
  name,
  amount: { $lte: max },
  unit
}))

And we can use the following in the $filter condition

[{
  "$and": [
    { "$eq": ["name", "xxx"] },
    { "$lte"  ["amount", 100 },
    { "$eq": ["unit", "myUnit"] }
  ]
},
{
  "$and": [
    { "$eq": ["$$this.name", "yyy"] },
    { "$lte":  ["$$this.amount", 90 },
    { "$eq": ["$$this.unit", "myUnit"] }
  ]
}]

/* example js implementation */
const filterConditions = externalArray.map(({ name, max, unit }) => ({
  $and: [
    { $eq: ['$$this.name', name] },
    { $lte:  ['$$this.amount', max },
    { $eq: ['$$this.unit', unit] }
  ]
}))

Then finally we can use the following aggregation

db.collection.aggregate([
  {
    $match: {
      myList: {
        $elemMatch: {
          $or: matchConditions
        }
      }
    }
  },
  {
    $addFields: {
      myList: {
        $filter: {
          input: '$myList',
          cond: {
            $or: filterConditions
          }
        }
      }
    }
  }
])

This is a very elegant solution, thank you. – colinrussl Jun 06 '20 at 09:05 — colinrussl, Jun 06 '20 at 09:05

MongoDB - Conditionally match two arrays

2 Answers2