Let's say I have following documents:

[
  {
    "order_id": 1,
    "user_id": 1001,
    "refund_id": 123
  },
  {
    "order_id": 2,
    "user_id": 1002
  },
  {
    "order_id": 3,
    "user_id": 1001
  },
  {
    "order_id": 4,
    "user_id": 1001
  },
  {
    "order_id": 5,
    "user_id": 1003,
    "refund_id": 111

  }
]

for given "refund_id": 123, I get user 1001, and I want to return all orders for user 1001 (order 1, 3, 4) SQL equivalent is something like

SELECT * from orders where user_id in (select user_id from orders where refund_id = 123);

CodePudding user response：

Here's one way you could do it.

db.collection.aggregate([
  {
    "$match": {
      "refund_id": 123
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "localField": "user_id",
      "foreignField": "user_id",
      "as": "orders"
    }
  }
])

Example output:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "order_id": 1,
    "orders": [
      {
        "_id": ObjectId("5a934e000102030405000000"),
        "order_id": 1,
        "refund_id": 123,
        "user_id": 1001
      },
      {
        "_id": ObjectId("5a934e000102030405000002"),
        "order_id": 3,
        "user_id": 1001
      },
      {
        "_id": ObjectId("5a934e000102030405000003"),
        "order_id": 4,
        "user_id": 1001
      }
    ],
    "refund_id": 123,
    "user_id": 1001
  }
]

Try it on mongoplayground.net.