comparing a string field to a sub-array field in mongodb

Question

There are 2 collections with each containing details as below:

Collection 1 (prpackages): {Server, Package, Version}
Collection 2 (qapackages): {Server, Package, Version}.

I need to get the package name from collection 1 and compare it against the same package on collection 2 using the version number (string field).

I used $lookup in aggregation to merge the 2 collections and the matching packages gets append as a array, but when I try to compare the string values of the field and sub array field using $match, it returns a no values.

MongoDB Enterprise > db.prpackages.find()

{ "_id" : ObjectId("596e627f392ae96de6a40762"), "server" : "ln1244567", "installedTime" : "1393542346", "package" : "hd-str-sa-config-sysctl_conf", "version" : "1"}

{ "_id" : ObjectId("596e627f392ae96de6a40763"), "server" : "ln1244567", "installedTime" : "1393542347", "package" : "hd-str-sa-config-authorized_keys", "version" : "1"}

{ "_id" : ObjectId("596e627f392ae96de6a40764"), "server" : "ln1244567", "installedTime" : "1393542348", "package" : "hd-str-sa-config-ntp_conf", "version" : "1"}

{ "_id" : ObjectId("596e627f392ae96de6a40765"), "server" : "ln1244567", "installedTime" : "1393542350", "package" : "hd-str-sa-config-rootcron", "version" : "1"}

{ "_id" : ObjectId("596e6280392ae96de6a40766"), "server" : "ln1244567", "installedTime" : "1393542352", "package" : "hd-str-sa-config-ntpd", "version" : "1"}

MongoDB Enterprise > db.qapackages.find()

{ "_id" : ObjectId("596e630c392ae96de6a40776"), "server" : "ln1244234", "installedTime" : "1399324250", "package" : "hd-str-sa-config-sysctl_conf", "version" : "1"}

{ "_id" : ObjectId("596e630c392ae96de6a40777"), "server" : "ln1244234", "installedTime" : "1399324251", "package" : "hd-str-sa-config-authorized_keys", "version" : "1"}

{ "_id" : ObjectId("596e630d392ae96de6a40778"), "server" : "ln1244234", "installedTime" : "1399324252", "package" : "hd-str-sa-config-ntp_conf", "version" : "1.2.1"}

{ "_id" : ObjectId("596e630d392ae96de6a40779"), "server" : "ln1244234", "installedTime" : "1399324254", "package" : "hd-str-sa-config-rootcron", "version" : "2"}

{ "_id" : ObjectId("596e630e392ae96de6a4077a"), "server" : "ln1244234", "installedTime" : "1399324255", "package" : "hd-str-sa-config-ntpd", "version" : "1"}

Merged using aggregate and lookup, the results looks like:

MongoDB Enterprise > db.prpackages.aggregate([ {$lookup: {from: "qapackages", localField: "package", foreignField: "package", as: "qacoll"}} ])

{ "_id" : ObjectId("596e627f392ae96de6a40762"), "server" : "ln1244567", "installedTime" : "1393542346", "package" : "hd-str-sa-config-sysctl_conf", "versionNum" : "1", "qacoll" : [ { "_id" : ObjectId("596e630c392ae96de6a40776"), "server" : "ln1244234", "installedTime" : "1399324250", "package" : "hd-str-sa-config-sysctl_conf", "versionNum" : "1" ] }

{ "_id" : ObjectId("596e627f392ae96de6a40763"), "server" : "ln1244567", "installedTime" : "1393542347", "package" : "hd-str-sa-config-authorized_keys", "versionNum" : "1", "qacoll" : [ { "_id" : ObjectId("596e630c392ae96de6a40777"), "server" : "ln1244234", "installedTime" : "1399324251", "package" : "hd-str-sa-config-authorized_keys", "versionNum" : "1"} ] }

{ "_id" : ObjectId("596e627f392ae96de6a40764"), "server" : "ln1244567", "installedTime" : "1393542348", "package" : "hd-str-sa-config-ntp_conf", "versionNum" : "1", "qacoll" : [ { "_id" : ObjectId("596e630d392ae96de6a40778"), "server" : "ln1244234", "installedTime" : "1399324252", "package" : "hd-str-sa-config-ntp_conf", "versionNum" : "1.2.1"} ] }

{ "_id" : ObjectId("596e627f392ae96de6a40765"), "server" : "ln1244567", "installedTime" : "1393542350", "package" : "hd-str-sa-config-rootcron", "versionNum" : "1", "qacoll" : [ { "_id" : ObjectId("596e630d392ae96de6a40779"), "server" : "ln1244234", "installedTime" : "1399324254", "package" : "hd-str-sa-config-rootcron", "versionNum" : "2"} ] }

{ "_id" : ObjectId("596e6280392ae96de6a40766"), "server" : "ln1244567", "installedTime" : "1393542352", "package" : "hd-str-sa-config-ntpd", "versionNum" : "1", "qacoll" : [ { "_id" : ObjectId("596e630e392ae96de6a4077a"), "server" : "ln1244234", "installedTime" : "1399324255", "package" : "hd-str-sa-config-ntpd", "versionNum" : "1"} ] }

Queries:

Works when using a value for comparison:

db.prpackages.aggregate([ {$lookup: {from: "qapackages", localField: "package", foreignField: "package", as: "qacoll"}}, {$match: {"qacoll.version": {$eq:  "1"}}} ])

Not working when compared against another field of the subarray:

db.prpackages.aggregate([ {$lookup: {from: "qapackages", localField: "package", foreignField: "package", as: "qacoll"}}, {$match: {"qacoll.version": {$eq:  "$version"}}} ])

What's going wrong?