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.

Can the "output.fields.name" be anything ? it sounds like it's not a constraint. — Tom Slabbaert
– Tom Slabbaert, Commented Mar 25, 2021 at 15:52
@TomSlabbaert Yes it can be anything. I'm looking for those whose name value is last_name_hebrew — T THE R
– T THE R, Commented Mar 29, 2021 at 7:55

turivishal · Accepted Answer · 2021-04-04 13:11:39Z

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

Tom Slabbaert · Accepted Answer · 2021-03-25 15:56:02Z

1

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.

answered Mar 25, 2021 at 15:56

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

4 Comments

Tal Rofe Over a year ago

I opened your playground link and your query seems to be wrong because the input name is last_name_hebrew with test1 text, but there is no any match in your output fields. The matching name there is last_name_hebrew, but the text is test21 which is not equal to test1

Tom Slabbaert Over a year ago

Firstly you can just change the code there, secondly output and input are arrays, because you didn't provide extended information on how many items can be there I assumed it could be more than 1.

Tal Rofe Over a year ago

You are to one who answered. If your solution is wrong - you need to change it. Did you understand that your solution is wrong?

T THE R Over a year ago

As stated in the comments - this solution does not do the thing I asked..

Collectives™ on Stack Overflow

MongoDB get all documents where array values match

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related