Q:
MongoDB: Select * from a table that contains both unique and non-unique entries
I am in a situation where I have two collection, one for unique values and the other for non-unique entries.
I am looking for a way to get the records from both collections, however, I need to fetch the values from only the first collection (I have taken only unique values) for each key.
Say, I have two collection and they look like this:
uniqueCollection = {
key1 : A,
key2 : B,
key3 : C
}
nonUniqueCollection = {
key1 : A,
key2 : B,
key3 : C,
key4 : D
}
Now, I need to write a query to retrieve only the unique collection values, that is A and B.
The reason why I have two collections is because they cannot have duplicate entries, one contains unique values and the other one contains non-unique entries.
I can use $unwind or $lookup for my requirement, but this would first result to two arrays, one containing unique values and the other non-unique values. Then I would need to merge them with $fiter. Then it would mean I would lose my index and would have to get each and every value from the database. This approach will be time consuming.
In an ideal scenario, I should have the key-value pairs as below:
key1 : A,
key2 : B,
key3 : C,
key4 : D
How can I get the desired result in one single query?
A:
So I came up with this solution and it works as expected. It is simple and as I wanted in the beginning. The only drawback is that it will query your data twice. Please let me know if you can suggest improvements to this.
I will first explain the data model.
key1 is the key and can contain multiple values (only one value for each key)
key2 is the key and can contain multiple values (only one value for each key)
key3 is the key and can contain multiple values (only one value for each key)
key4 is the key and can contain multiple values (only one value for each key)
uniqueCollection is the name for collection which can contain only unique values (only one value for each key). For non-unique values, I use a mapping (associative array) to store multiple values for a single key.
nonUniqueCollection is the name for collection which can contain multiple values for a single key.
Example 1: Let's say you have three documents in your database
/* 1 */
{
"key1" : "A",
"key2" : "B",
"key3" : "C",
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a19"),
"unique" : [
{
"key1" : "A",
"key2" : "B",
"key3" : "C",
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a20"),
"value" : "value1"
}
]
}
/* 2 */
{
"key1" : "A",
"key2" : "B",
"key3" : "C",
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a21"),
"unique" : [
{
"key1" : "A",
"key2" : "B",
"key3" : "C",
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a22"),
"value" : "value2"
},
{
"key1" : "A",
"key2" : "B",
"key3" : "C",
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a23"),
"value" : "value3"
}
]
}
/* 3 */
{
"key1" : "A",
"key2" : "B",
"key3" : "C",
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a24"),
"unique" : [
{
"key1" : "A",
"key2" : "B",
"key3" : "C",
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a25"),
"value" : "value4"
}
]
}
So, uniqueColl will contain only unique values (value1, value2, value3), while nonUniqueColl contains both unique and non-unique values (value1, value2, value3, value4)
In my implementation, I use $arrayToObject operator to convert both the collections into an object with the keys as unique and non-unique. To explain further, if key1 is unique and key2 is unique or non-unique, $arrayToObject operator will convert the object to an object containing key1, key2, key3, key4 and value. See the explanation below:
{
"key1" : "A",
"key2" : "B",
"key3" : "C",
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a19"),
"unique" : [
{
"key1" : "A",
"key2" : "B",
"key3" : "C",
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a20"),
"value" : "value1"
}
]
}
This above object is converted to an object which looks like this:
{
"key1" : "A",
"key2" : "B",
"key3" : "C",
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a19"),
"unique" : [
{
"key1" : "A",
"key2" : "B",
"key3" : "C",
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a20"),
"value" : "value1"
}
]
}
Same for the other objects. Now, using $filter operator, I can get the desired result. See the below code.
db.getCollection('uniqueCollection').aggregate([
{$match: {_id: ObjectId("5adbbc7c8dcd2e25ebf39a19")}},
{$lookup: {from: "nonUniqueCollection", localField: "key1", foreignField: "key1", as: "value"}},
{$lookup: {from: "nonUniqueCollection", localField: "key2", foreignField: "key2", as: "value"}},
{$lookup: {from: "nonUniqueCollection", localField: "key3", foreignField: "key3", as: "value"}},
{$project: {unique: {_id: 0, key1: "$$value.key1", key2: "$$value.key2", key3: "$$value.key3"}, value: 1}}
]);
This will result to a collection as below.
/* 1 */
{
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a19"),
"unique" : [
{
"key1" : "A",
"key2" : "B",
"key3" : "C",
"_id" : ObjectId("5adbbc7c8dcd2e25ebf39a20"),
"value" : "value1"
}
]
}
So, using $arrayToObject, I can get the unique values from this collection as follows.
db.getCollection('uniqueCollection').aggregate([
{$match: {_id: ObjectId("5adbbc7c8dcd2e25ebf39a19")}},
{$lookup: {from: "nonUniqueCollection", localField: "key1", foreignField: "key1", as: "value"}},
{$lookup: {from: "nonUniqueCollection", localField: "key2", foreignField: "key2", as: "value"}},
{$lookup: {from: "non
