mongo how to load nested document by aggregation

Question

I have 3 kinds of document listed as below:

vilya_be> db.plots.find({})
[
  {
    _id: ObjectId("6426b069ca83da130cdb7f70"),
    x: 0,
    y: 0,
    _class: 'com.vilya.farm.domain.model.Plot'
  }
]

vilya_be> db.users_have_plots.find({})
[
  {
    _id: ObjectId("6426b073ca83da130cdb7f71"),
    userId: '6412c76956d4170a7de34d92',
    plot: DBRef("plots", ObjectId("6426b069ca83da130cdb7f70")),
    _class: 'com.vilya.farm.domain.model.UserPlot'
  }
]

vilya_be> db.users.find({})
[
  {
    _id: ObjectId("6412c76956d4170a7de34d92"),
    email: '[email protected]',
    password: '$2a$10$s9VgOYd.fOKZF66TnAsjWemiCYkA7aG45NJpuSNgbVxpcIGF7fWqu',
    firstName: 'f',
    lastName: 'l',
    plots: [ DBRef("users_have_plots", ObjectId("6426b073ca83da130cdb7f71")) ],
    _class: 'com.vilya.farm.domain.model.User'
  },
  {
    _id: ObjectId("6414667360e4ba4481052627"),
    email: '[email protected]',
    password: '$2a$10$OP52phZ61l2JX2e2TQOu9ubYFBYcPeqEZ92ox2Nyyp5e.MEZk7GhS',
    firstName: 'f',
    lastName: 'l',
    _class: 'com.vilya.farm.domain.model.User'
  }
]

I want to load all users along with their users_have_plots (along with their plots) on a single command.

I have tried:

db.users.aggregate([
{
  "$lookup": {
    "from": "users_have_plots",
    "let": {
      "plots": "$plots"
    },
    "pipeline": [
      {
        "$match": {
          "$expr": {
            "$in": [
              "$_id",
              "$$plots"
            ]
          }
        }
      },
      {
        "$lookup": {
          "from": "plots",
          "localField": "plot",
          "foreignField": "_id",
          "as": "plot"
        }
      }
    ],
    "as": "plots"
  }
}
]);

It gives me: PlanExecutor error during aggregation :: caused by :: $in requires an array as a second argument, found: missing

And this:

db.users.aggregate([
{
  "$lookup": {
    "from": "users_have_plots",
    "let": {
      "plots": "$plots"
    },
    "pipeline": [
      {
        "$match": {
          "$expr": {
            "$in": [
              "$_id",
              "$$plots.ObjectId"
            ]
          }
        }
      },
      {
        "$lookup": {
          "from": "plots",
          "localField": "plot",
          "foreignField": "_id",
          "as": "plot"
        }
      }
    ],
    "as": "plots"
  }
}
]);

It also gives me: PlanExecutor error during aggregation :: caused by :: $in requires an array as a second argument, found: missing

I'm new to mongodb and just cannot find anyway to make it works. Any help will be appreciate!

mongo version: mongo:6.0.2-focal running on docker desktop

EDIT: Tried:

db.users.aggregate([{
  "$lookup": {
    "from": "users_have_plots",
    "let": {
      "plots": "$plots"
    },
    "pipeline": [
      {
        "$match": {
          "$expr": {
            "$in": [
              "$_id",
              {
                "$ifNull": [
                  "$$plots.ObjectId",
                  []
                ]
              }
            ]
          }
        }
      },
      {
        "$lookup": {
          "from": "plots",
          "localField": "plot",
          "foreignField": "_id",
          "as": "plot"
        }
      }
    ],
    "as": "plots"
  }
}
])

Giving me:

[
  {
    _id: ObjectId("6412c76956d4170a7de34d92"),
    email: '[email protected]',
    password: '$2a$10$s9VgOYd.fOKZF66TnAsjWemiCYkA7aG45NJpuSNgbVxpcIGF7fWqu',
    firstName: 'f',
    lastName: 'l',
    plots: [],
    _class: 'com.vilya.farm.domain.model.User'
  },
  {
    _id: ObjectId("6414667360e4ba4481052627"),
    email: '[email protected]',
    password: '$2a$10$OP52phZ61l2JX2e2TQOu9ubYFBYcPeqEZ92ox2Nyyp5e.MEZk7GhS',
    firstName: 'f',
    lastName: 'l',
    _class: 'com.vilya.farm.domain.model.User',
    plots: []
  }
]

No plots included.

Answer 1

I think you are using Dbrefs as defined here in the documentation. That's why your queries are not working, because DBRef store the documents in this format, internally:

db={
  "plots": [
    {
      _id: ObjectId("6426b069ca83da130cdb7f70"),
      x: 0,
      y: 0,
      _class: "com.vilya.farm.domain.model.Plot"
    }
  ],
  "users_have_plots": [
    {
      _id: ObjectId("6426b073ca83da130cdb7f71"),
      userId: "6412c76956d4170a7de34d92",
      plot: {
        "$ref": "plots",
        "$id": ObjectId("6426b069ca83da130cdb7f70")
      },
      _class: "com.vilya.farm.domain.model.UserPlot"
    }
  ],
  "users": [
    {
      _id: ObjectId("6412c76956d4170a7de34d92"),
      email: "[email protected]",
      password: "$2a$10$s9VgOYd.fOKZF66TnAsjWemiCYkA7aG45NJpuSNgbVxpcIGF7fWqu",
      firstName: "f",
      lastName: "l",
      plots: [
        {
          "$ref": "users_have_plots",
          "$id": ObjectId("6426b073ca83da130cdb7f71")
        }
      ],
      _class: "com.vilya.farm.domain.model.User"
    },
    {
      _id: ObjectId("6414667360e4ba4481052627"),
      email: "[email protected]",
      password: "$2a$10$OP52phZ61l2JX2e2TQOu9ubYFBYcPeqEZ92ox2Nyyp5e.MEZk7GhS",
      firstName: "f",
      lastName: "l",
      _class: "com.vilya.farm.domain.model.User"
    }
  ]
}

Note the plot field in user_have_plots collection and plots field in the user collection. They are an object. To make it work, try this:

db.users.aggregate([
  {
    "$lookup": {
      "from": "users_have_plots",
      "let": {
        "plots": "$plots"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$_id",
                {
                  "$ifNull": [
                    "$$plots.$id",
                    []
                  ]
                }
              ]
            }
          }
        },
        {
          "$lookup": {
            "from": "plots",
            "localField": "plot.$id",
            "foreignField": "_id",
            "as": "plot"
          }
        }
      ],
      "as": "plots"
    }
  }
])

Playground link.

Finally, you don't need to use Dbrefs, they are suitable for purposes when you have joined a single property with multiple collections, or with collections in multiple databases. All your collections are in the same databases, use simple references.