MongoDB聚合 – 根据字段是否存在进行条件$lookup

huangapple go评论64阅读模式
英文:

MongoDB Aggregation - Conditional $lookup depending if a field exists or not

问题

我有两个名为collection1collection2的集合,它们的结构如下所示,

// collection1

[
  { "_id": 100, "text": "i am good", "history":[{"date":"2023-6-1", "updated":"2023-6-2"}] },
  { "_id": 101, "text": "i am naughty"}
]

// collection2

[
  { "_id": 1,collection1Id:100, "date":"2023-6-1", "updated":"2023-6-2" },  // is present in 1 so should not goto this collection
  { "_id": 2,collection1Id:101, "date":"2023-7-3", "updated":"2023-7-5" }
]

我希望聚合查询运行如下,如果字段(history)在collection1中不存在,则应进行查找collection2。请参考下面的代码:

db.collection1.aggregate([

如果在collection1中不存在history
{
从collection2查找
}

])

期望的输出

执行聚合后,我希望结果如下所示:

[
  { "_id": 100, "text": "i am good", "history":[{"date":"2023-6-1", "updated":"2023-6-2"}] },
  { "_id": 2,"text": "i am naughty", "history":[{"_id": 2, "collection1Id": 101, "date":"2023-7-3", "updated":"2023-7-5"}] } // history has been taken from collection 2 
]

第一行仅来自collection1,因为history存在,而第二条记录来自collection2,因为在主要集合中不存在history字段。

英文:

I have two collections called collection1, and collection2 and they are structured as follows,

// collection1

[
  { "_id": 100, "text": "i am good", "history":[{"date":"2023-6-1", "updated":"2023-6-2"}] },
  { "_id": 101, "text": "i am naughty"}
]

// collection2

[
  { "_id": 1,collection1Id:100, "date":"2023-6-1", "updated":"2023-6-2" },  // is present in 1 so should not goto this collection
  { "_id": 2,collection1Id:101, "date":"2023-7-3", "updated":"2023-7-5" }
]

I want the aggregation query to run such that if the field (history) does not exist inside collection1 then lookup to collection2 should be made. See the code below:

db.collection1.aggreagte([

if history is not exist in collection1
{
lookup from collection2
}

])

Expected Output

After the aggregation executes, I want the result to be shown below:

[
  { "_id": 100, "text": "i am good", "history":[{"date":"2023-6-1", "updated":"2023-6-2"}] },
  { "_id": 2,"text": "i am naughty", "history":[{"_id": 2, collection1Id: 101, "date":"2023-7-3", "updated":"2023-7-5"}],  } // history has been taken from collection 2 
]

The first row is from collection1 only because history exists and the second record is from collection2 where I did lookup as the history field did not exist in the primary collection.

答案1

得分: 3

我不认为可以在条件下进行 $lookup 连接另一个集合。

但是,您可以在 history 字段不存在时创建它,然后将其赋值为 collection2 的值。否则,保持其现有值。

db.collection1.aggregate([
  {
    "$lookup": {
      "from": "collection2",
      "localField": "_id",
      "foreignField": "collection1Id",
      "as": "collection2"
    }
  },
  {
    $set: {
      history: {
        "$cond": {
          "if": {
            $eq: [
              {
                $type: "$history"
              },
              "missing"
            ]
          },
          "then": "$collection2",
          "else": "$history"
        }
      }
    }
  },
  {
    $unset: "collection2"
  }
])

在 Mongo Playground 上查看演示

英文:

I don't think that is possible to conditional $lookup join another collection.

But, you can achieve create the history field if the history field is missing then assign it with the collection2 value. Otherwise, remain its existing value.

db.collection1.aggregate([
  {
    "$lookup": {
      "from": "collection2",
      "localField": "_id",
      "foreignField": "collection1Id",
      "as": "collection2"
    }
  },
  {
    $set: {
      history: {
        "$cond": {
          "if": {
            $eq: [
              {
                $type: "$history"
              },
              "missing"
            ]
          },
          "then": "$collection2",
          "else": "$history"
        }
      }
    }
  },
  {
    $unset: "collection2"
  }
])

Demo @ Mongo Playground

huangapple
  • 本文由 发表于 2023年6月1日 16:27:53
  • 转载请务必保留本文链接:https://go.coder-hub.com/76380003.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定