Mongodb lookup pipeline, comparing a field with an array-CodePudding

I have this sample data:

[
  {
    "customers": [
      {"id": 100, "name": "a"},
      {"id": 200, "name": "b"},
      {"id": 300, "name": "c"},
      {"id": 400, "name": "d"}
    ],
    "sales": [
      {
        "sale_id": 9999,
        "persons_related": [
          {"id": 100},
          {"id": 900},
          {"id": 800}
        ]
      },
      {
        "sale_id": 9998,
        "persons_related": [
          {"id": 500},
          {"id": 550},
          {"id": 560}
        ]
      },      
    ]
  }
]

It represents two collections, customers and sales.

Imagine that I am working with the customers collection, I have selected just the customer 100

db.collection.aggregate([
  { $project: {
      "customer": { "$arrayElemAt": [ "$customers", 0 ]
  }}
}

Which returns:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "customer": {
      "id": 100,
      "name": "a"
    }
  }
]

And I want to find up the sales where this id appears, so I lookup against the same collection, adding this stage to the pipeline:

  { $lookup: {
      from: "collection",
      let: { id: "$customer.id" },
      pipeline: [
        { $match: {
            $and: [
              { $expr: { $in: [ "$$id", "$sales.persons_related.id" ] } }
            ]
        }}
      ],
      as: "sales"
    }
  }

I need to use this lookup version (the one with let and pipeline, and not the other one with localField/foreignField) because I need to add additional filters in the match stage of the pipeline. However, this part:

{ $expr: { $in: [ "$$id", "$sales.persons_related.id" ] } }

Doesn't work as expected, I have tried with other operators ($eq) with same result.

The expected output (using a pipeline in the lookup) should be:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "customer": {
      "id": 100,
      "name": "a"
    },
    "sales": [
      {
        "sale_id": 9999,
        "persons_related": [
          {"id": 100},
          {"id": 900},
          {"id": 800}
        ]
      }
    ]
  }
]

Can you please lend me a hand? You can test on this mongo playground

CodePudding user response：

Just FYI mongoplayground provides a multiple collection option, so instead of trying to hack your syntax to simulate it you can just us the dropdown at the top right side to change it.

Your syntax is fine, here is a working playground example:

db.customers.aggregate([
  {
    $match: {
      id: 100
    }
  },
  {
    $lookup: {
      from: "sales",
      let: {
        id: "$id"
      },
      pipeline: [
        {
          $match: {
            $and: [
              {
                $expr: {
                  $in: [
                    "$$id",
                    "$persons_related.id"
                  ]
                }
              }
            ]
          }
        }
      ],
      as: "sales"
    }
  }
])

$lookup also flattens arrays so you can just use the simpler syntax for it and it simplifies the code:

db.customers.aggregate([
  {
    $match: {
      id: 100
    }
  },
  {
    $lookup: {
      from: "sales",
      localField: "id",
      foreignField: "persons_related.id",
      as: "sales"
    }
  }
])

Mongo Playground