mongodb - Is a localField $lookup faster than a pipeline? - Stack Overflow

admin管理员组

文章数量:1405903

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?

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?

• mongodb
• mongodb-query
• data-modeling
• database-performance

Share Improve this question Follow asked Mar 7 at 7:28 ciscis 1,3911818 silver badges6161 bronze badges 1

• 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 { "$lookup": { "from": "mycollection", "localField": "_id", "foreignField": "sharedWith", "as": "sharedItems" } }. See: mongoplayground/p/c7h_UOTohmt – aneroid Commented Mar 7 at 8:00

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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"
    }
  }
])

Mongo Playground

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).

本文标签： mongodbIs a localField lookup faster than a pipelineStack Overflow