MongoDB get all documents where array values match

Question

I have the following structure of document:

  {
    "input": {
      "fields": [
        {
          "name": "last_name_hebrew",
          "text": "test1",
        },
      ],
    },
    "output": {
      "fields": [
        {
          "name": "last_name_hebrew",
          "text": "test1"
        },
      ],
    },
  },

I want to get all documents, where fields has object that has name of value last_name_hebrew as with text value of the output.fields.

For example in the given structure it would return this documents because input.fields.name is last_name_hebrew and text is equal to the text in output.

Note I cannot guarantee that fields array in either input or output will have name: last_name_hebrew in the array.

How can I do so?

This is my try to first force the arrays to have document with name of last_name_hebrew:

db.collection.find({
  "input.fields": {
    $elemMatch: {
      "name": "last_name_hebrew"
    }
  },
  "output.fields": {
    $elemMatch: {
      "name": "last_name_hebrew"
    }
  },
  
})

But now I need to compare the text values.

user_10770370 · Answer

You will have to use an aggregation pipeline to achieve this, there are several ways to do so, here is one example:

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $gt: [
          {
            $size: {
              $filter: {
                input: "$input.fields",
                as: "inputField",
                cond: {
                  $and: [
                    {
                      $eq: [
                        "$$inputField.name",
                        "last_name_hebrew"
                      ]
                    },
                    {
                      "$setIsSubset": [
                        [
                          "$$inputField.text"
                        ],
                        "$output.fields.text"
                      ]
                    }
                  ]
                }
              }
            }
          },
          0
        ]
      }
    }
  }
])

Mongo Playground One thing to note is that with this query there are no restrictions on the output.fields.name (as it was not required), if you do require the names to match then you can drop the .text field in the $setIsSubset operator.

user_8987128 · Answer

Your first 2 condition with $elemMatch is correct
add expression match, first find the matching element that having last_name_hebrew name from input using $filter and get first element from that filtered result using $arrayElemAt, same process for output field and then match both object using $eq

db.collection.find({
  "input.fields": { $elemMatch: { "name": "last_name_hebrew" } },
  "output.fields": { $elemMatch: { "name": "last_name_hebrew" } },
  $expr: {
    $eq: [
      {
        $arrayElemAt: [
          {
            $filter: {
              input: "$input.fields",
              cond: { $eq: ["$$this.name", "last_name_hebrew"] }
            }
          },
          0
        ]
      },
      {
        $arrayElemAt: [
          {
            $filter: {
              input: "$output.fields",
              cond: { $eq: ["$$this.name", "last_name_hebrew"] }
            }
          },
          0
        ]
      }
    ]
  }
});

Playground

Second option: if you want to go with more specific to match exact 2 fields name and text both just need to add $let operator to return fields from filter,

db.collection.find({
  "input.fields": { $elemMatch: { "name": "last_name_hebrew" } },
  "output.fields": { $elemMatch: { "name": "last_name_hebrew" } },
  $expr: {
    $eq: [
      {
        $let: {
          vars: {
            input: {
              $arrayElemAt: [
                {
                  $filter: {
                    input: "$input.fields",
                    cond: { $eq: ["$$this.name", "last_name_hebrew"] }
                  }
                },
                0
              ]
            }
          },
          in: { name: "$$input.name", text: "$$input.text" }
        }
      },
      {
        $let: {
          vars: {
            output: {
              $arrayElemAt: [
                {
                  $filter: {
                    input: "$output.fields",
                    cond: { $eq: ["$$this.name", "last_name_hebrew"] }
                  }
                },
                0
              ]
            }
          },
          in: { name: "$$output.name", text: "$$output.text" }
        }
      }
    ]
  }
})

Playground

Third option: for more specific to check both fields in loop,

first filter the matching elements by name in input field using $filter
pass above filter result in another filter
filter to match name and text field in output field, if its not [] empty then return filter result
$ne to check return result is not [] empty

db.collection.find({
  "input.fields": { $elemMatch: { "name": "last_name_hebrew" } },
  "output.fields": { $elemMatch: { "name": "last_name_hebrew" } },
  $expr: {
    $ne: [
      {
        $filter: {
          input: {
            $filter: {
              input: "$input.fields",
              cond: { $eq: ["$$this.name", "last_name_hebrew"] }
            }
          },
          as: "i",
          cond: {
            $ne: [
              {
                $filter: {
                  input: "$output.fields",
                  cond: {
                    $and: [
                      { $eq: ["$$this.name", "$$i.name"] },
                      { $eq: ["$$this.text", "$$i.text"] }
                    ]
                  }
                }
              },
              []
            ]
          }
        }
      },
      []
    ]
  }
})

Playground

MongoDB get all documents where array values match

2 Answers