Mongo lookup through array of ids-CodePudding

how to correct lookup collection product which has array of ids of prices and need execute query exactly from prices collection query. Need to show price record with lookup product record so, I have prices record

{
"_id" : "813f02ff-882e-44f7-b2bc-2f067427daf6",
"unit_amount" : 333,
"currency" : "USD",
"interval" : "year",
"active" : true
}

and product

"_id" : "3c46f277-8953-4f96-baf1-bd871ee3301f",
"name" : "test",
"prices" : [ 
    "813f02ff-882e-44f7-b2bc-2f067427daf6", 
    "f5c76122-6132-4e4b-a26b-41bbd6325acc", 
    "3e4be68e-fbed-47f7-b871-92de72cb00df"
]

and my query, I thought it should be like that

db.getCollection('price').aggregate([
{
        "$lookup": {
            "from": "product",
            "let": { "prid": "$_id" },
            "pipeline": [
                { "$match": { "$expr": { "$in": ["$$prid", '$prices'] } } }
              ],
            "as": "product_tbl"
        }
    }    
])

faced with

{
    "ok" : 0,
    "errmsg" : "PlanExecutor error during aggregation :: caused by :: $in requires an array as a second argument, found: missing",
    "code" : 40081,
    "codeName" : "Location40081"
}

but it's not works. How it shoul be look ?

CodePudding user response：

Seems like some of the documents in your product collection are missing prices key. You can try this:

db.prices.aggregate([
  {
    "$lookup": {
      "from": "product",
      "let": {
        "prid": "$_id"
      },
      "pipeline": [
        {
          "$addFields": {
            "prices": {
              "$ifNull": [
                "$prices",
                []
              ]
            }
          }
        },
        {
          "$match": {
            "$expr": {
              "$in": [
                "$$prid",
                "$prices"
              ]
            }
          }
        }
      ],
      "as": "product_tbl"
    }
  }
])

Here, we recompute the prices and set it to empty array, if it's missing, before the $match. Playground link.