MongoDb LEFT Join return data not same like MySQl Left JOIN

Question

I have two MongoDB Tabel

App Tabel

{
  "_id": {
    "$oid": "5cbac9172d962331401d0e48"
  },
  "appInstallCount": {
    "$numberInt": "6"
  },
  "status": {
    "$numberInt": "1"
  },
  "appName": "PC Access Log",
  "appInfo": "You can view users who access your PC",
  "appImageUrl": "assets/images/app/userlog-app-thismypc.png",
  "userID": "5c3d6a25227a1d01eba0afc9",
  "version": "1",
  "released_date": {
    "$date": {
      "$numberLong": "1555745047247"
    }
  },
  "lastUpdate_date": {
    "$date": {
      "$numberLong": "1555745047247"
    }
  },
  "__v": {
    "$numberInt": "0"
  }
}{
  "_id": {
    "$oid": "5cbd300316e4651c78167478"
  },
  "appInstallCount": {
    "$numberInt": "0"
  },
  "status": {
    "$numberInt": "1"
  },
  "appName": "PC Access Log TEST",
  "appInfo": "You can view users who access your PC",
  "appImageUrl": "assets/images/app/userlog-app-thismypc.png",
  "userID": "5c3d6a25227a1d01eba0afc9",
  "version": "1",
  "released_date": {
    "$date": {
      "$numberLong": "1555745047247"
    }
  },
  "lastUpdate_date": {
    "$date": {
      "$numberLong": "1555745047247"
    }
  },
  "__v": {
    "$numberInt": "0"
  }
}

and UserAndApps Tabel

{
  "_id": {
    "$oid": "5cbd327294877a70b510c8b6"
  },
  "status": {
    "$numberInt": "1"
  },
  "userID": {
    "$oid": "5cbd2d3e0afcb16efa795563"
  },
  "appID": {
    "$oid": "5cbac9172d962331401d0e48"
  },
  "date": {
    "$date": {
      "$numberLong": "1555903090765"
    }
  },
  "__v": {
    "$numberInt": "0"
  }
}{
  "_id": {
    "$oid": "5cbd327394877a70b510c8b7"
  },
  "status": {
    "$numberInt": "1"
  },
  "userID": {
    "$oid": "5cbd2d3e0afcb16efa795563"
  },
  "appID": {
    "$oid": "5cbd300316e4651c78167478"
  },
  "date": {
    "$date": {
      "$numberLong": "1555903091265"
    }
  },
  "__v": {
    "$numberInt": "0"
  }
}{
  "_id": {
    "$oid": "5cbd329a94877a70b510c8b9"
  },
  "status": {
    "$numberInt": "1"
  },
  "userID": {
    "$oid": "5c3d6a25227a1d01eba0afc9"
  },
  "appID": {
    "$oid": "5cbac9172d962331401d0e48"
  },
  "date": {
    "$date": {
      "$numberLong": "1555903130918"
    }
  },
  "__v": {
    "$numberInt": "0"
  }
}{
  "_id": {
    "$oid": "5cbd329b94877a70b510c8ba"
  },
  "status": {
    "$numberInt": "1"
  },
  "userID": {
    "$oid": "5c3d6a25227a1d01eba0afc9"
  },
  "appID": {
    "$oid": "5cbd300316e4651c78167478"
  },
  "date": {
    "$date": {
      "$numberLong": "1555903131796"
    }
  },
  "__v": {
    "$numberInt": "0"
  }
}

I use this code to join above two tabels

[
  {
    '$lookup': {
      'from': 'userandapps', 
      'localField': '_id', 
      'foreignField': 'appID', 
      'as': 'appData'
    }
  }, {
    '$unwind': {
      'path': '$appData', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$match': {
      'appData.userID': new ObjectId('5cbd2d3e0afcb16efa795563')
    }
  }
]

The issue is it only returns that fill appData array, but I need that appData array to fill and empty both stages like in MYSQL LEFT JOIN. if 'appData.userID': new ObjectId('5cbd2d3e0afcb16efa795563') not belong one of document. it also returns with an empty appData array. I tried many ways but I did not get any way to work this out. can someone help me with this? Big Help.

I try With this code and it returns empty without any data

    [{
    $lookup: {
        from: 'userandapps',
        localField: '_id',
        foreignField: 'appID',
        as: 'appData'
    }
}, {
    $unwind: {
        path: "$appData",
        preserveNullAndEmptyArrays: true

    }
}, {
    $match: {
        "$or": [{
            'appData.userID': ObjectId('5cbd2d3e0afcb16efa795563')
        }, {
            'appData': null
        }]
    }
}]

Because you did not include `null`. Correct with `{'$match': { "$or": [ { 'appData.userID': new ObjectId('5cbd2d3e0afcb16efa795563') ], { "appData": null }] } }`. The `null` is what you asked for from the `preserveNullAndEmptyArrays` so you basically have **two** conditions to look for. `$lookup` itself **is a left join** by definition. — Neil Lunn, Apr 23 '19 at 09:28
@NeilLunn `[ { '$lookup': { 'from': 'userandapps', 'localField': '_id', 'foreignField': 'appID', 'as': 'appData' } }, { '$unwind': { 'path': '$appData', 'preserveNullAndEmptyArrays': true } }, { '$match': { '$or': [ { 'appData.userID': new ObjectId('5cbd2d3e0afcb16efa795563'), 'appData': null } ] } } ]` It give me emty data — Supun Abesekara, Apr 23 '19 at 10:01
Because that's not the statement I wrote. Read the comment again and spot the difference. I use `$or` because there are **two** conditions, each in their **own document** as part of the predicate. — Neil Lunn, Apr 23 '19 at 10:16
@NeilLunn Good Sir I think there is some error in the code `Expected "[" or AggregationStage but "{" found. ` Can you kindly look that for me? Thanks — Supun Abesekara, Apr 23 '19 at 10:22
`{{ '$match': { "$or": [ { 'appData.userID': new ObjectId('5cbd2d3e0afcb16efa795563') }, { "appData": null }] } }` Had an extra `]` in there. I originally wrote that with an `$in`, but an `$or` is the more accurate statement. — Neil Lunn, Apr 23 '19 at 10:26
You also have a basic problem here is that you are probably **too literal** in trying to reproduce your MySQL result. Instead of using `$unwind` at all, you should really simply leave the array as is and actually "pre-filter" the result, leaving matches and/or empty arrays. [Aggregation filter after $lookup](https://stackoverflow.com/a/36465475/2313887) demonstrates common approaches to "filtering" the `$lookup` result, which you really should keep as an array unless the "filtered" results would cause the document to exceed the 16MB BSON limit. — Neil Lunn, Apr 23 '19 at 10:28
@NeilLunn Good Sir I try your code and it works for me (Thanks). But the thing is it returns `{ "appData": null }`but I need if `'appData.userID': new ObjectId('5cbd2d3e0afcb16efa795563') ` not belong one of document. it also returns with empty `appData` array. Do you have any idea how I achieve that's? — Supun Abesekara, Apr 24 '19 at 02:34
By not using `$unwind` or at least adding the `$match` before the `$unwind`. The `$unwind` "denormalizes" the content ( just like a SQL join would ) but the default behavoir is simply the "array" as the many within the matched parent. If all you want are documents with "at least one of the members" equal to the given `userID` value, OR where no related members appeared at all, then just `{ "$match": { "$or": [{ "appData.userID": new ObjectId('5cbd2d3e0afcb16efa795563') }, { "appData": [] } ] } }` to match an array containing that value OR empty array since the foreign key was not matched. — Neil Lunn, Apr 24 '19 at 22:16
Of course that means `$lookup` then `$match` **only**, and DON'T use `$unwind`. And that's a bit different to a `LEFT JOIN` since you would not get that result without a pretty complex statement. But here it's pretty much the expected behavior "out of the box", and mostly because the `$lookup` result is an "array", just as if you had embedded the documents in the first place. — Neil Lunn, Apr 24 '19 at 22:19

MongoDb LEFT Join return data not same like MySQl Left JOIN

0 Answers0