0

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?

Improve this question
3
  • format please your data as code. It would be more readable. Commented Jul 26, 2017 at 18:25
  • You actually want $filter in a regular $addFields or $project here. Possibly $redact if you simply mean to remove both parent and child if the condition is not met. $match does not "use field values" and works just like a regular .find(). Use the other operators for field comparison. Commented Jul 26, 2017 at 23:04
  • Thanks a lot for your explanation. I am able to figure it out, plus I learnt that its a duplicate of the question. stackoverflow.com/questions/25509644/… Commented Jul 28, 2017 at 18:43
Related questions
940
How can I list all collections in the MongoDB shell?
1990
How to query MongoDB with "like"
76
Mongodb Join on _id field from String to ObjectId
Load 6 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.