使用引用表中的条件进行mongodb聚合查找

avwztpqn  于 2023-03-07  发布在  Go
关注(0)|答案(1)|浏览(154)

我曾尝试在mongodb中从一个表和另一个表中查找值,但没有得到预期输出
I have author table with some my fieldsI have document table with array author_id field contain value reference to author.id

db.documents.aggregate([
        {
            $lookup: {
                    from: "author",
                    localField: "author_id",
                    foreignField: "_id",
                    as: "author",
            }
        },
        {
            $project : {
                author_id: 0
            }
        },
        {
            $skip: 0
        },
        {
            $limit: 2
        }
    ]
)

这是我的代码结合2表获得数据

[
  {
    "_id": {"$oid": "63fd8a80ae2c18ab3b81a4b1"},
    "author": [
      {
        "_id": {"$oid": "63fd80b2381bff5ebf42cd61"},
        "first_name": "Junior",
        "last_name": "Neymar",
        "address": "Brazil",
        "is_deleted": false
      },
      {
        "_id": {"$oid": "63fd812787493aea44d4af48"},
        "first_name": "cristiano",
        "last_name": "Ronaldo",
        "address": "Protugal",
        "is_deleted": true
      }
    ],
    "language": "Brazil",
    "pages": 200,
    "title": "Document 3"
  },
  {
    "_id": {"$oid": "63fd8a96ae2c18ab3b81a4b2"},
    "author": [],
    "language": "Brazil",
    "pages": 200,
    "title": "Document 2"
  }
]

我得到了这个输出,但我不想让作者谁有is_deleted字段是真的,在我的输出请帮助我!

prdp8dxp

prdp8dxp1#

您可以使用$unwind来简化数据筛选

db.documents.aggregate([
  {
    $lookup: {
      from: "author",
      localField: "author_id",
      foreignField: "_id",
      as: "author_id",
      
    }
  },
  {
    "$unwind": "$author_id"
  },
  {
    $match: {
      "author_id.is_deleted": false
    }
  },
  {
    $project: {
      author_id: 0
    }
  },
  {
    $skip: 0
  },
  {
    $limit: 2
  }
])

MONGO PLAYGROUND
也可以使用管道来进行过滤

db.documents.aggregate([
  {
    $lookup: {
      from: "author",
      localField: "author_id",
      foreignField: "_id",
      pipeline: [
        {
          $match: {
            "is_deleted": false
          }
        }
      ],
      as: "author_id",
      
    }
  },
  {
    "$unwind": "$author_id"
  },
  {
    $project: {
      author_id: 0
    }
  },
  {
    $skip: 0
  },
  {
    $limit: 2
  }
])

MONGO PLAYGROUND

相关问题