MongoDB Aggregation - match documents with array of objects, by another array of objects filter

Question

I have documents that consist of an array of objects, and each object in this array consists of another array of objects.
For simplicity, irrelevant fields of the documents were omitted.

It looks like this (2 documents):

{
  title: 'abc',
  parts: [
    {
      part: "verse",
      progressions: [
        {
          progression: "62a4a87da7fdbdabf787e47f",
          key: "Ab",
          _id: "62b5aaa0c9e9fe8a7d7240d3"
        },
        {
          progression: "62adf477ed11cbbe156d5769",
          key: "C",
          _id: "62b5aaa0c9e9fe8a7d7240d3"
        },
      ],
      _id: "62b5aaa0c9e9fe8a7d7240d2"
    },
    {
      part: "chorus",
      progressions: [
        {
          progression: "62a4a51b4693c43dce9be09c",
          key: "E",
          _id: "62b5aaa0c9e9fe8a7d7240d9"
        }
      ],
      _id: "62b5aaa0c9e9fe8a7d7240d8"
    }
  ],
}

{
  title: 'def',
  parts: [
    {
      part: "verse",
      progressions: [
        {
          progression: "33a4a87da7fopvvbf787erwe",
          key: "E",
          _id: "62b5aaa0c9e9fe8a7d7240d3"
        },
        {
          progression: "98opf477ewfscbbe156d5442",
          key: "Bb",
          _id: "62b5aaa0c9e9fe8a7d7240d3"
        },
      ],
      _id: "12r3aaa0c4r5me8a7d72oi8u"
    },
    {
      part: "bridge",
      progressions: [
        {
          progression: "62a4a51b4693c43dce9be09c",
          key: "C#",
          _id: "62b5aaa0c9e9fe8a7d7240d9"
        }
      ],
      _id: "62b5aaa0rwfvse8a7d7240d8"
    }
  ],
}

The parameters that the client sends with a request are an array of objects:

[
  { part: 'verse', progressions: ['62a4a87da7fdbdabf787e47f', '62a4a51b4693c43dce9be09c'] },
  { part: 'chorus', progressions: ['62adf477ed11cbbe156d5769'] }
]

I want to retrieve, through mongodb aggregation, the documents that at least one of objects in the input array above is matching them:
In this example, documents that have in their parts array field, an object that has the value 'verse' in the part property and one of the progressions id's ['62a4a87da7fdbdabf787e47f', '62a4a51b4693c43dce9be09c'] in the progression property in one of the objects in the progressions property, or documents that have in their parts array field, an object that has the value 'chorus' in the part property and one of the progressions id's ['62adf477ed11cbbe156d5769'] in the progression property in one of the objects in the progressions property.
In this example, the matching document is the first one (with the title 'abc'), but in actual use, there might be many matching documents.

I tried to create an aggregation pipeline myself (using the mongoose 'aggregate' method):

// parsedProgressions = [
//   { part: 'verse', progressions: ['62a4a87da7fdbdabf787e47f', '62a4a51b4693c43dce9be09c'] },
//   { part: 'chorus', progressions: ['62adf477ed11cbbe156d5769'] }
// ]
songs.aggregate([
  {
    $addFields: {
      "tempMapResults": {
        $map: {
          input: parsedProgressions,
          as: "parsedProgression",
          in: {
            $cond: {
              if: { parts: { $elemMatch: { part: "$$parsedProgression.part", "progressions.progression": mongoose.Types.ObjectId("$$parsedProgression.progression") } } },
              then: true, else: false
            }
          }
        }
      }
    }
  },
  {
    $addFields: {
      "isMatched": { $anyElementTrue: ["$tempMapResults"] }
    }
  },
  { $match: { isMatched: true } },
  { $project: { title: 1, "parts.part": 1, "parts.progressions.progression": 1 } }
]);

But it didn't work - as I understand it, because the $elemMatch can be used only in the $match stage.
Anyway, I guess I overcomplicated the aggregation pipeline, so I will be glad if you can fix my aggregation pipeline/offer a better working one.

nimrod serok · Accepted Answer · 2022-06-28 09:39:04Z

This is not a simple case as these are both nested arrays and we need to match both the part and the progressions, which are not on the same level

One option looks complicated a bit, but keeps your data small:

In order to make things easier, $set a new array field called matchCond which includes an array called progs containing the parts.progressions. To each sub-object inside it insert the matching progressions input array. We do need to be careful here and handle the case where there is no matching progressions input arrayprogressions input array, as this is the case for the "bridge" part on the second document.
Now we just need to check if for any of these progs items, the progression field is matching one option in input array. This is done using $filter, and $rediceing the number of results.
Just match document which have results and format the answer

db.collection.aggregate([
  {
    $set: {
      matchCond: {
        $map: {
          input: "$parts",
          as: "parts",
          in: {progs: {
              $map: {
                input: "$$parts.progressions",
                in: {$mergeObjects: [
                    "$$this",
                    {input: {progressions: []}},
                    {input: {$first: {
                          $filter: {
                            input: inputData,
                            as: "inputPart",
                            cond: {$eq: ["$$inputPart.part", "$$parts.part"]}
                          }
                     }}}
                ]}
              }
          }}
        }
      }
    }
  },
  {$set: {
      matchCond: {
        $reduce: {
          input: "$matchCond",
          initialValue: 0,
          in: {$add: [
              "$$value",
              {$size: {
                  $filter: {
                    input: "$$this.progs",
                    as: "part",
                    cond: {$in: ["$$part.progression", "$$part.input.progressions"]}
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {$match: {matchCond: {$gt: 0}}},
  {$project: {title: 1, parts: 1}}
])

See how it works on the playground example

Another option is to use $unwind, which looks simple, but will duplicate your data, thus, likely to be slower:

db.collection.aggregate([
  {$addFields: {inputData: inputData, cond: "$parts"}},
  {$unwind: "$cond"},
  {$unwind: "$cond.progressions"},
  {$unwind: "$inputData"},
  {$match: {
      $expr: {
        $and: [
          {$eq: ["$cond.part", "$inputData.part"]},
          {$in: ["$cond.progressions.progression", "$inputData.progressions"]}
        ]
      }
    }
  },
  {$project: {title: 1, parts: 1}}
])

See how it works on the playground example - unwind

There are several options between these two...

Thank you very much, I used the first option (since I understood it's more performant). After posting the question I noticed that I actually need to fetch the documents that match all the objects in the parsedProgressions and not just at least one of them, but changing the matchCond: {$gt: 0} to matchCond: { $eq: parsedProgressions.length } solved it.

Collectives™ on Stack Overflow

MongoDB Aggregation - match documents with array of objects, by another array of objects filter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related