mongodb lookup using multiple fields not working

user_5981188 0
Viewed 65

I am trying to get some other information from titleInfo collection using siteId of regCodes collection

I have two collections

regCodes

{
    "siteId" : "123A",
    "registration_code" : "ABC",
    "used_flag" : true,
    "Allowed_Use" : 1,
    "Remaining_Use" : 0,
    "BatchId" : "SNGL",
    "CodeDuration" : 180
}

titleInfo

{
    "title" : "Principles of Microeconomics",
    "product_form_detail" : "EPUB",
    "final_binding_description" : "Ebook",
    "vitalsource_enabled" : false,
    "reading_line" : "with InQuizitive and Smartwork5",
    "volume" : "",
    "protected_content" : {
        "ebookSiteIds" : [ 
            "123A"
        ],
        "studySpaceSiteIds" : [],
        "iqSiteIds" : []
    }
}

below query not working, getting 'regcodeData' as empty array.

using mongodb version 3.6.18

db.getCollection('regCodes').aggregate([
  {
    $match: {
      registration_code: 'ABC'
    }
  },
  {
    $lookup: {
      from: "titleInfo",
      let: {
        regcode_siteId: "$siteId"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $or: [
                {
                  $eq: [
                    "$protected_content.ebookSiteIds",
                    "$$regcode_siteId"
                  ]
                },
                {
                  $eq: [
                    "$protected_content.studySpaceSiteIds",
                    "$$regcode_siteId"
                  ]
                },
                {
                  $eq: [
                    "$protected_content.iqSiteIds",
                    "$$regcode_siteId"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "regcodeData"
    }
  }
])

below query is working as expected

db.getCollection('titleInfo').find({
  $or: [
    {
      "protected_content.ebookSiteIds": "123A"
    },
    {
      "protected_content.studySpaceSiteIds": "123A"
    },
    {
      "protected_content.iqSiteIds": "123A"
    }
  ]
})
mongodb
2 Answers

You just need to unwind the arrays, by using $unwind operator with preserveNullAndEmptyArrays option set to true.

Updated Query:

db.regCodes.aggregate([
  {
    $match: {
      registration_code: "ABC"
    }
  },
  {
    $lookup: {
      from: "titleInfo",
      let: {
        regcode_siteId: "$siteId"
      },
      pipeline: [
        {
          $unwind: {
            path: "$protected_content.ebookSiteIds",
            preserveNullAndEmptyArrays: true
          }
        },
        {
          $unwind: {
            path: "$protected_content.studySpaceSiteIds",
            preserveNullAndEmptyArrays: true
          }
        },
        {
          $unwind: {
            path: "$protected_content.iqSiteIds",
            preserveNullAndEmptyArrays: true
          }
        },
        {
          $match: {
            $expr: {
              $or: [
                {
                  $eq: [
                    "$protected_content.ebookSiteIds",
                    "$$regcode_siteId"
                  ]
                },
                {
                  $eq: [
                    "$protected_content.studySpaceSiteIds",
                    "$$regcode_siteId"
                  ]
                },
                {
                  $eq: [
                    "$protected_content.iqSiteIds",
                    "$$regcode_siteId"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "regcodeData"
    }
  }
])

MongoPlayGroundLink

user_66354640

My bad trying to match array with string

Answer is as below

db.getCollection('regCodes').aggregate([
  {
    $match: {
      registration_code: 'ABC'
    }
  },
  {
    $lookup: {
      from: "titleInfo",
      let: {
        regcode_siteId: "$siteId"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $or: [
                {
                  $in: [
                    "$$regcode_siteId",
                    "$protected_content.ebookSiteIds"
                  ]
                },
                {
                  $in: [
                    "$$regcode_siteId",
                    "$protected_content.studySpaceSiteIds"
                  ]
                },
                {
                  $in: [
                    "$$regcode_siteId",
                    "$protected_content.iqSiteIds"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "regcodeData"
    }
  }
])
user_59811880
Related