mongodb mongo如何通过聚合加载嵌套文档

5tmbdcev  于 2023-04-20  发布在  Go
关注(0)|答案(1)|浏览(157)

我有3种文件如下所列:

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: 'abc@abc.com',
    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: 'abc1@abc.com',
    password: '$2a$10$OP52phZ61l2JX2e2TQOu9ubYFBYcPeqEZ92ox2Nyyp5e.MEZk7GhS',
    firstName: 'f',
    lastName: 'l',
    _class: 'com.vilya.farm.domain.model.User'
  }
]

我想用一个命令加载所有的users沿着users_have_plots(以及plots)。
我试过:

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"
  }
}
]);

它给了我:PlanExecutor error during aggregation :: caused by :: $in requires an array as a second argument, found: missing
还有这个

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"
  }
}
]);

它也给了我:PlanExecutor error during aggregation :: caused by :: $in requires an array as a second argument, found: missing
我是mongodb的新手,只是找不到任何方法来使它工作。任何帮助都将不胜感激!
mongo版本:mongo:6.0.2-focal在docker桌面上运行
编辑:尝试:

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"
  }
}
])

给我:

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

未包括地块。

x4shl7ld

x4shl7ld1#

我认为你使用的Dbrefs是在documentation中定义的。这就是为什么你的查询不起作用,因为DBRef在内部以这种格式存储文档:

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: "abc@abc.com",
      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: "abc1@abc.com",
      password: "$2a$10$OP52phZ61l2JX2e2TQOu9ubYFBYcPeqEZ92ox2Nyyp5e.MEZk7GhS",
      firstName: "f",
      lastName: "l",
      _class: "com.vilya.farm.domain.model.User"
    }
  ]
}

注意user_have_plots集合中的plot字段和user集合中的plots字段。它们是一个对象。要使其工作,请尝试以下操作:

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.
最后,你不需要使用Dbrefs,当你将一个属性与多个集合或多个数据库中的集合连接时,它们很适合。所有的集合都在同一个数据库中,使用简单的引用。

相关问题