mongodb - Best Mongo Aggregation to Map Array of _ids in One Collection To Values from Another

I have two related mongo collections. These example documents illustrate their relation:

// "things" collection example document:
{
  _id: 1,
  categories: [123, 234],
  // other fields
}

// "categories" collection example documents:
{
  _id: 123,
  value: "Category name"
},
{
  _id: 234,
  value: "Other category name"
}

I've been trying to find a way to map the id numbers in the categories array of a document from things to the values from the corresponding documents in the categories collection. Per the above example, you would end up with this document:

{
  _id: 1,
  categories: [
    "Category name",
    "Other category name",
  ],
  // other fields
}

My problem is that my current pipeline is overly-complicated and surely performing unnecessary operations, creating potential performance issues. My current pipeline is:

(starting point)

{
  _id: 1,
  categories: [123, 234],
  // other fields
}

$unwind categories

{
  _id: 1,
  categories: 123,
  // other fields
},
{
  _id: 1,
  categories: 234,
  // other fields
}

$lookup on categories collection matching the new local "categories" field to the foreign "_id"

{
  _id: 1,
  categories: [{ _id: 123, value: "Category name" }],
  // other fields
},
{
  _id: 1,
  categories: [{ _id: 234, value: "Other category name" }],
  // other fields
}

$addFields with { $arrayElemAt: [ "$categories", 0 ] } to replace the array with the document I wanted in the first place

{
  _id: 1,
  categories: { _id: 123, value: "Category name" },
  // other fields
},
{
  _id: 1,
  categories: { _id: 234, value: "Other category name" },
  // other fields
}

$addFields with { categories: "$categories.value" } to replace the entire document with just the value field

{
  _id: 1,
  categories: "Category name",
  // other fields
},
{
  _id: 1,
  categories: "Other category name",
  // other fields
}

$group to "undo" the original unwind. I'm using _id: "$_id" and { $addToSet: "$categories" } (and maaany other properties in the format <field-name>: { $first: "$<field-name>" } to re-add all the "other fields")

{
  _id: 1,
  categories: [
    "Category name",
    "Other category name",
  ],
  // other fields
}

I'm worried I'm missing aggregate functions that are much more efficient and thus creating slow and costly read operations when I use this on a large number of documents in the future but I'm failing to find cleaner solutions. Any nudge in the right direction would be highly appreciated.

I have two related mongo collections. These example documents illustrate their relation:

// "things" collection example document:
{
  _id: 1,
  categories: [123, 234],
  // other fields
}

// "categories" collection example documents:
{
  _id: 123,
  value: "Category name"
},
{
  _id: 234,
  value: "Other category name"
}

{
  _id: 1,
  categories: [
    "Category name",
    "Other category name",
  ],
  // other fields
}

My problem is that my current pipeline is overly-complicated and surely performing unnecessary operations, creating potential performance issues. My current pipeline is:

(starting point)

{
  _id: 1,
  categories: [123, 234],
  // other fields
}

$unwind categories

{
  _id: 1,
  categories: 123,
  // other fields
},
{
  _id: 1,
  categories: 234,
  // other fields
}

$lookup on categories collection matching the new local "categories" field to the foreign "_id"

{
  _id: 1,
  categories: [{ _id: 123, value: "Category name" }],
  // other fields
},
{
  _id: 1,
  categories: [{ _id: 234, value: "Other category name" }],
  // other fields
}

$addFields with { $arrayElemAt: [ "$categories", 0 ] } to replace the array with the document I wanted in the first place

{
  _id: 1,
  categories: { _id: 123, value: "Category name" },
  // other fields
},
{
  _id: 1,
  categories: { _id: 234, value: "Other category name" },
  // other fields
}

$addFields with { categories: "$categories.value" } to replace the entire document with just the value field

{
  _id: 1,
  categories: "Category name",
  // other fields
},
{
  _id: 1,
  categories: "Other category name",
  // other fields
}

$group to "undo" the original unwind. I'm using _id: "$_id" and { $addToSet: "$categories" } (and maaany other properties in the format <field-name>: { $first: "$<field-name>" } to re-add all the "other fields")

{
  _id: 1,
  categories: [
    "Category name",
    "Other category name",
  ],
  // other fields
}

Share Improve this question asked 12 hours ago dinx 1231 silver badge6 bronze badges

1 a lookup and a map should do mongoplayground/p/X50lanhepXC – cmgchess Commented 12 hours ago
That's amazing, thank you! Didn't know about the website either. – dinx Commented 12 hours ago
yeah its a cool website for trying out queries. if u want u can write an answer – cmgchess Commented 12 hours ago
2 For the second step, you can skip the $map and use just { $set: { categories: "$categories.value" } }. Since categories is still an array after the lookup, doing .value gives an "array of that field". mongoplayground/p/4_ThoM4G4Dk. In other situations, it may not be that simple, so it's good you've learned about $map. – aneroid Commented 12 hours ago
Actually for this case it would be recommended to denormalize and embed the categories inside the things collection directly. – ray Commented 11 hours ago

Add a comment |

1 Answer 1

Sorted by: Reset to default 1

per @cmgchess -

This can all be done in two steps:

(starting point)

{
  _id: 1,
  categories: [123, 234],
  // other fields
}

$lookup (without unwinding)

{
  _id: 1,
  categories: [
    {
      _id: 123,
      value: "Category name"
    },
    {
      _id: 234,
      value: "Other category name"
    }
  ],
  // other fields
}

$set using $map

// operation:
{
  $set: {
    categories: {
      $map: {
        input: "$categories",
        as: "category",
        in: "$$category.value"
      }
    }
  }
}

// result:
{
  _id: 1,
  categories: [
    "Category name",
    "Other category name"
  ],
  // other fields
}

paydirt. :)

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

mongodb - Best Mongo Aggregation to Map Array of _ids in One Collection To Values from Another - Stack Overflow

1 Answer 1

与本文相关的文章

评论列表(0)