javascript 如何根据类型从集合中有条件地查找(mongo4.4)

下面是一些中间管道的聚合

[
 {
    $unwind: "$destinations"
  },
  {
    $lookup: {
      from: "customers",
      localField: "destinations.sold_to_id",
      foreignField: "_id",
      as: "sold_to_ref"
    },
    
  },
]

根据上面的查询，destinations是一个数组，如下所示

[{
   type: 1,
   sold_to_id: 'xxxxx'
 },
 {
  type: 2,
  sold_to_id: 'yyyy',
 }
]

现在我想根据type（ie）执行查找，如果type = 1，则从customers查找，否则从users查找。注意：customers和users是两个不同的集合
请帮我在这。
先谢谢你了
编辑：
我尝试的解决方案

[{
    $unwind: "$destinations"
},
{
    $lookup: {
      from: "customers",
      "let": {
        type: "$destinations.type",
        destination_id: "$destinations.sold_to_id"
      },
      pipeline: [
        {
          "$match": {
            "$expr": { $and:
                       [
                         { $eq: [ "$$type", 1 ] }, // Here I need to compare the type value with 1 which is not happening
                         { $eq: [ "$_id", "$$destination_id" ] }
                       ]
                    }
          }
        }
      ],
      as: "sold_to_ref"
    },
    
  }]

编辑：如果输入集合类似于

db={
  "collection": [
    { 
      "contact": [
        'adf', 'dsf', 'sdd'],
      "destinations": [
        {
          type: 1,
          sold_to_id: "xxxxx"
        },
        {
          type: 1,
          sold_to_id: "yyyy"
        },
        {
          type: 1,
          sold_to_id: "zzz"
        },
        {
          type: 1,
          sold_to_id: "zzz"
        }
      ]
    }
  ],
  "customers": [
    {
      _id: "xxxxx",
      name: "Customer1"
    },
    {
      _id: "yyyy",
      name: "Customer2"
    }
  ],
  "users": [
    {
      _id: "xxxxx",
      name: "User1"
    },
    {
      _id: "yyyy",
      name: "User2"
    }
  ]
}

则结果应如下

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "contact": [
        'adf', 'dsf', 'sdd'],
      "destinations": [
        {
          type: 1,
          sold_to_id: "xxxxx"
        },
        {
          type: 1,
          sold_to_id: "yyyy"
        },
        {
          type: 1,
          sold_to_id: "zzz"
        },
        {
          type: 1,
          sold_to_id: "zzz"
        }
      ]
    "sold_to_ref": [
      {
        "_id": "xxxxx",
        "name": "Customer1"
      }
    ]
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "contact": [
        'adf', 'dsf', 'sdd'],
      "destinations": [
        {
          type: 1,
          sold_to_id: "xxxxx"
        },
        {
          type: 1,
          sold_to_id: "yyyy"
        },
        {
          type: 1,
          sold_to_id: "zzz"
        },
        {
          type: 1,
          sold_to_id: "zzz"
        }
      ]
    "sold_to_ref": [
      {
        "_id": "yyyy",
        "name": "Customer2"
      }
    ]
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "contact": [
        'adf', 'dsf', 'sdd'],
      "destinations": [
        {
          type: 1,
          sold_to_id: "xxxxx"
        },
        {
          type: 1,
          sold_to_id: "yyyy"
        },
        {
          type: 1,
          sold_to_id: "zzz"
        },
        {
          type: 1,
          sold_to_id: "zzz"
        }
      ]
    "sold_to_ref": []
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "contact": [
        'adf', 'dsf', 'sdd'],
      "destinations": [
        {
          type: 1,
          sold_to_id: "xxxxx"
        },
        {
          type: 1,
          sold_to_id: "yyyy"
        },
        {
          type: 1,
          sold_to_id: "zzz"
        },
        {
          type: 1,
          sold_to_id: "zzz"
        }
      ]
    "sold_to_ref": []
  }
]

不可能（即使在SQL中也不可能）。

**解决方法：**使用$facet运行2个LEFT JOINS，合并它们并展平结果。

db.collection.aggregate([
  {
    $facet: {
      customers: [
        {
          $addFields: {
            destinations: {
              $filter: {
                input: "$destinations",
                cond: {
                  $eq: [
                    "$$this.type",
                    1
                  ]
                }
              }
            }
          }
        },
        {
          $lookup: {
            from: "customers",
            localField: "destinations.sold_to_id",
            foreignField: "_id",
            as: "sold_to_ref"
          }
        },
        {
          $match: {
            "sold_to_ref.0": {
              $exists: true
            }
          }
        }
      ],
      users: [
        {
          $addFields: {
            destinations: {
              $filter: {
                input: "$destinations",
                cond: {
                  $eq: [
                    "$$this.type",
                    2
                  ]
                }
              }
            }
          }
        },
        {
          $lookup: {
            from: "users",
            localField: "destinations.sold_to_id",
            foreignField: "_id",
            as: "sold_to_ref"
          }
        },
        {
          $match: {
            "sold_to_ref.0": {
              $exists: true
            }
          }
        }
      ]
    }
  },
  {
    $project: {
      merge: {
        "$concatArrays": [
          "$users",
          "$customers"
        ]
      }
    }
  },
  {
    $unwind: "$merge"
  },
  {
    "$replaceWith": {
      "$mergeObjects": [
        "$merge",
        {
          "copy": "$merge.destinations"
        }
      ]
    }
  },
  {
    $unwind: "$copy"
  },
  {
    $addFields: {
      copy: "$$REMOVE",
      sold_to_ref: {
        $filter: {
          input: "$sold_to_ref",
          cond: {
            $eq: [
              "$copy.sold_to_id",
              "$$this._id"
            ]
          }
        }
      }
    }
  }
])

MongoPlayground

javascript 如何根据类型从集合中有条件地查找(mongo4.4)

1条答案

不可能（即使在SQL中也不可能）。

相关问题

热门标签

最新问答