MongoDB find all documents where property is not equals to another nested property-CodePudding

here are my documents

  {
    "p1": {
      "lastName": "Manson"
    },
    "l1": [
      {
        "lastName": "Manson"
      }
    ]
  },
  {
    "p1": {
      "lastName": "revah"
    },
    "l1": [
      {
        "lastName": "Manson"
      }
    ]
  }

I want to get all documents where p1.lastName != l1.lastName

Should return:

{ "p1": { "lastName": "revah" }, "l1": [ { "lastName": "Manson" } ] }

CodePudding user response：

l1 is an array, hence it can contain more than one option.

If we want to find only document in which p1.lastName is different from all options in l1.lastName, we can use:

db.collection.aggregate([
  {$match: {$expr: {$not: {$in: ["$p1.lastName", "$l1.lastName"]}}}}
])

See how it works on the playground example - all options.

But if we can assume l1 always contains only one item, we can use:

db.collection.aggregate([
  {$match: {$expr: {$ne: ["$p1.lastName", {$first: "$l1.lastName"}]}}}
])

See how it works on the playground example - one option.

The $expr allows comparing to values inside the document.