In MongoDB (version 8) I'm wondering about the most read-efficient way of storing a "sharing mechanism". I.e. the users can share items with other items within the same collection.
When fetching a single item, we need to including all the items that got shared with the item in question. On the item that gets shared we would need to add an array like sharedWith
. The data might look like so:
[
{
"_id": "1",
"value": "value_of_1",
"sharedWith": [
"2"
]
},
{
"_id": "2",
"value": "value_of_2",
"sharedWith": []
}
]
Now, let's say we want the item with _id
2 and we need the items which shared itself to that item. A query would look like so:
db.mycollection.aggregate([
{
$match: {
_id: "2"
}
},
{
"$lookup": {
"from": "mycollection",
let: {
idVar: "$_id"
},
pipeline: [
{
$match: {
$expr: {
$in: [
"$$idVar",
"$sharedWith"
]
}
}
}
],
"as": "sharedItems"
}
}
])
Now, I'm wondering if it would be faster to store an additional property like gotSharedFrom
on the "sharing receiver" so that the data would be
[
{
"_id": "1",
"value": "value_of_1",
"sharedWith": [
"2"
],
"gotSharedFrom": []
},
{
"_id": "2",
"value": "value_of_2",
"sharedWith": [],
"gotSharedFrom": [
"1"
]
}
]
And now the $lookup
can be simplified to:
db.mycollection.aggregate([
{
$match: {
_id: "2"
}
},
{
"$lookup": {
"from": "mycollection",
"localField": "gotSharedFrom",
"foreignField": "_id",
"as": "sharedItems"
}
}
])
I know that maintaining both sharedWith
and gotSharedFrom
and keep them in sync means some hassle. So, question is: Is it significantly faster when reading? Can you create more efficient indexes? Is it worth the effort?
1. Entirely depends on usage. The way gotSharedFrom
works in the second example, you don't need the reverse sharedWith
.
2. Your lookup in the first example can be simplified to the opposite of the second example and doesn't need a pipeline, since the array lookup works in both directions. Simply put it as:
db.mycollection.aggregate([
{
$match: { _id: "2" }
},
{
"$lookup": {
"from": "mycollection",
"localField": "_id",
"foreignField": "sharedWith",
"as": "sharedItems"
}
}
])
3. Since both pipelines are now equally simple/straightforward, you can choose the model which suits your needs. Just make sure to add an index for the sharedWith
field (or gotSharedFrom
if that's what you will use).