1

(Edit : this question was edited to better reflect the issue, which might be a little more complicated than the proposed related question.)

Let's say I have these two collections

products

{
  _id: 'AAAA',
  components: [
    { type: 'foo', items: [
      { itemId: 'item1', qty: 2 },
      { itemId: 'item2', qty: 1 }
    ] },
    { type: 'bar', items: [
      { itemId: 'item3', qty: 8 }
    ] }
  ]
}

items

{
  _id: 'item1',
  name: 'Foo Item'
}
{
  _id: 'item2',
  name: 'Bar Item'
}
{
  _id: 'item3',
  name: 'Buz Item'
}

And that I perform this query

db['products'].aggregate([
  { $lookup: { 
    from: 'items',
    localField: 'components.items.itemId',
    foreignField: '_id',
    as: 'componentItems'
  } }
]);

I get this

{
  _id: 'AAAA',
  components: [
    { type: 'foo', items: [
      { itemId: 'item1', qty: 2 },
      { itemId: 'item2', qty: 1 }
    ] }
    { type: 'bar', items: [
      { itemId: 'item3', qty: 8 }
    ] }
  ],
  componentItems: [ ]
}

Why doesn't the aggregation read the local field value? How can I retrieve the foreign document without losing my original document structure?

Edit

I have read the jira issue and seen the proposed answer, however I don't know how this applies. This is not merely an array, but values from an object, inside an array. I am not sure how I can unwind this, and how to put it back together without losing the document structure.

Edit 2

The problem that I have is that I'm not sure how to group the results back together. With this query :

db['products'].aggregate([
  { $unwind: '$components' },
  { $unwind: '$components.items' },
  { $lookup: {
    from: 'items',
    localField: 'components.items.itemId',
    foreignField: '_id',
    as: 'componentsItems'
  } }
]);

I get the "correct" result of

{ "_id" : "AAAA", "components" : { "type" : "foo", "items" : { "itemId" : "item1", "qty" : 2 } }, "componentsItems" : [ { "_id" : "item1", "name" : "Foo Item" } ] }
{ "_id" : "AAAA", "components" : { "type" : "foo", "items" : { "itemId" : "item2", "qty" : 1 } }, "componentsItems" : [ { "_id" : "item2", "name" : "Bar Item" } ] }
{ "_id" : "AAAA", "components" : { "type" : "bar", "items" : { "itemId" : "item3", "qty" : 8 } }, "componentsItems" : [ { "_id" : "item3", "name" : "Buz Item" } ] }

But, while I can unwind components.items, I cannot seem to unto this, as $group complains that

"the group aggregate field name 'components.items' cannot be used because $group's field names cannot contain '.'"

db['products'].aggregate([
  { $unwind: '$components' },
  { $unwind: '$components.items' },
  { $lookup: {
    from: 'items',
    localField: 'components.items.itemId',
    foreignField: '_id',
    as: 'componentsItems'
  } },
  { "$group": {
    "components.type": "$components.type",
    "components.items": { $push: "$components.items" },
    "componentsItems": { $push: "$componentsItems" }
  } },
  { "$group": {
    "_id": "$_id",
    "components": { $push: "$components" },
    "componentsItems": { $push: "$componentsItems" }
  } }
]);

Edit 3

This query is, thus far, the closest that I found, except that components are not grouped back by type.

db['products'].aggregate([
  { $unwind: '$components' },
  { $unwind: '$components.items' },
  { $lookup: {
    from: 'items',
    localField: 'components.items.itemId',
    foreignField: '_id',
    as: 'componentsItems'
  } },
  { $unwind: '$componentsItems' },
  { $group: {
    "_id": "$_id",
    "components": {
      $push: {
        "type": "$components.type",
        "items": "$components.items"
      }
    },
    "componentsItems": { $addToSet: "$componentsItems" }
  } }
]);

Also: I am concerned that using $unwind and $group may affect the order of the components, which should be preserved. AFAIK, MongoDB preserve array order when storing documents. I'd hate for this functionality to be broken by the awkwardness of $lookup.

Yanick Rochon
  • 51,409
  • 25
  • 133
  • 214

1 Answers1

0

Here is my long and awkward solution :

db['products'].aggregate([
  // unwind all... because $lookup cannot work with multi-values
  { $unwind: '$components' },
  { $unwind: '$components.items' },

  // lookup... This is a 1:1 relationship but who cares, right?
  { $lookup: {
    from: 'items',
    localField: 'components.items.itemId',
    foreignField: '_id',
    as: 'componentsItems'
  } },

  // our 1:1 relationship is now an array, so this is required
  // before grouping, so we don't end up with array of arrays
  { $unwind: '$componentsItems' },

  // Group 1: put "components.items" in a temporary array
  //          and filter duplicates from "componentsItems"
  { $group: {
    "_id": {
      "i": "$_id",
      "t": "$components.type"
    },
    "items": {
      $push: "$components.items"
    },
    "componentsItems": { $addToSet: "$componentsItems" }
  } },

  // undo $push...
  { $unwind: "$componentsItems" },

  // Group 2: put everything back together
  { $group: {
    "_id": "$_id.i",
    "items": {
      $push: {
        "type": "$_id.t",
        "items": "$items"
      }
    },
    "componentsItems": { $push: "$componentsItems" }
  } }
]);

Edit

A better solution :

db['products'].aggregate([
  // Return document, added a collection of "itemId"
  { $project: {
    "_id": 1,
    "components": 1,
    "componentItemId": "$components.items.itemId"
  } },
  // Since there was two arrays, the field is an array of arrays...
  { $unwind: "$componentItemId" },
  { $unwind: "$componentItemId" },

  // make 1:1 lookup...
  { $lookup: {
    from: 'items',
    localField: 'componentItemId',
    foreignField: '_id',
    as: 'componentsItems'
  } },
  // ... extract the 1:1 reference...
  { $unwind: "$componentsItems" },

  // group back, ignoring the "componentItemId" field
  { $group: {
    "_id": "$_id",
    "components": { $first: "$components" },
    "componentItems": { $addToSet: "$componentsItems" }
  }}
]);

I'm not sure if there is yet a better solution, and I am concerned about performance, but this seems to be the only solutions I can think of.

The downside is that documents cannot be dynamic, and this query will need to be modified whenever the schema changes.

Update

This seems to be resolved in MongoDB 3.3.4 (not release at the time of writing this answer).

Yanick Rochon
  • 51,409
  • 25
  • 133
  • 214