How to join in mongodb an Array of Object references

Question

I have a collection of objects (recipe) that has an array of object references to another collection (ingredients). I want to join these two on the database layer, as opposed to the application layer. I believe the $aggregate is what I need to use but unfortunately haven't had much success. Following you can find my models, how I do the join in JS and how I got stuck trying to implement using $aggregate

TS Model:

export type Ingredient = {
  id?: string;
  name: string;
};

export type IngredientAmount = {
  ingredient: Ingredient;
  amount: number;
  unit: string;
};

export type Recipe {
  id?: number;
  name: string;
  steps: string[];
  ingredients: IngredientAmount[];
}

Since I need to do queries based on ingredients, i.e. "Give me all recipes using milk" I decided to using Document references. But now I'm having a hard time coming up with a query to do the join on this Array of Ingredient Amount.

This is how a DB entry looks like:

recipes: {
"_id": {
    "$oid": "5eab14eb597fdf1af2974a55"
},
"name": "Cheese & Ham sandwich",
"steps": ["Slice bread", "Put cheese slices and Ham between bread slices"],
"ingredients": [{
    "ingredientId": "5eab10d5597fdf1af2974a4f",
    "amt": "2",
    "unit": "slices"
}, {
    "ingredientId": "5eab10e5597fdf1af2974a50",
    "amt": "1",
    "unit": "unit"
}, {
    "ingredientId": "5eab10fc597fdf1af2974a51",
    "amt": "1",
    "unit": "slice"
},]}

ingredients: {
    "_id": {
        "$oid": "5eab10d5597fdf1af2974a4f"
    },
    "name": "cheese",
}

I want to query those two documents and join them according to my predefined type. This is how I do it in JS:

// Query recipe
const rawRecipe = await db
  .collection('recipes')
  .findOne(new ObjectID(queryId));

// Get ids to join
const ingredientIds = await rawRecipe['ingredients'].map(
  (i: { ingredientId: string }) => new ObjectID(i.ingredientId)
);

// Fetch the ingredients to be joined
const rawIngredients: Ingredient[] = await db
  .collection('ingredients')
  .find({ _id: { $in: ingredientIds } })
  .map((r) => {
    return {
      id: r._id.toString(),
      name: r.name,
    };
  })
  .toArray();

// Create objects from the items to be joined
const ingredients: IngredientAmount[] = rawIngredients.map((i) => {
  return {
    ingredient: i,
    amount: rawRecipe['ingredients'].find(
      (entry: { ingredientId: string }) => entry.ingredientId == i.id
    ).amt,
    unit: rawRecipe['ingredients'].find(
      (entry: { ingredientId: string }) => entry.ingredientId == i.id
    ).unit,
  };
});

// Create new result object
const r: Recipe = new Recipe(
  rawRecipe['name'],
  rawRecipe['steps'],
  ingredients,
  rawRecipe['_id']
);

What would be the equivalent using the aggregate pipeline?

I got as far as

[
  {
    '$unwind': {
      'path': '$ingredients'
    }
  }, {
    '$addFields': {
      'ingredientOid': {
        '$toObjectId': '$ingredients.ingredientId'
      }
    }
  }, {
    '$lookup': {
      'from': 'ingredients', 
      'localField': 'ingredientOid', 
      'foreignField': '_id', 
      'as': 'ingredientObj'
    }
  }, {
    '$unwind': {
      'path': '$ingredientObj'
    }
  },
]

but I got stuck into merging it back into a single document. Also, this doesn't feel very efficient either.

Is $group pipeline is what you are searching about ? You can group together all information and format the document output as you like. — hpapier
– hpapier, Commented May 6, 2020 at 10:30
@prasad_ I'm getting an array of recipes, each with one ingredient object. — David Anderson
– David Anderson, Commented May 6, 2020 at 10:33

David Anderson · Accepted Answer · 2020-06-05 18:03:08Z

Managed to find the answer.

As suggested by @hpapier I needed to group, and then use a replace root to make the object look like what I wanted

Here's how it was in the end:

[
{
  $unwind: {
    path: '$ingredients',
  },
},
{
  $addFields: {
    ingredientOid: {
      $toObjectId: '$ingredients.ingredientId',
    },
  },
},
{
  $lookup: {
    from: 'ingredients',
    localField: 'ingredientOid',
    foreignField: '_id',
    as: 'ingredientObj',
  },
},
{
  $unwind: {
    path: '$ingredientObj',
  },
},
{
  $group: {
    _id: {
      _id: '$_id',
      steps: '$steps',
      name: '$name',
    },
    ingredients: {
      $push: {
        amount: '$ingredients.amount',
        unit: '$ingredients.unit',
        ingredient: {
          _id: '$ingredientObj.id',
          name: '$ingredientObj.name',
        },
      },
    },
  },
},
{
  $replaceRoot: {
    newRoot: {
      _id: '$_id._id',
      steps: '$_id.steps',
      name: '$_id.name',
      ingredients: '$ingredients',
    },
  },
},

Posting here as an example of how to do a join and mutate the objects.

Collectives™ on Stack Overflow

How to join in mongodb an Array of Object references

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related