Joining two collections and possibly calculating totals

Working with Data
1

Hello,

I am new to MongoDB and want to design a database for my small food planning application.

I would like to join them with a lookup, so that ingredients array in meals collection becomes enriched with all the product information.

This is my playground:

Meals collection

{
    "_id":{"$oid":"635a732932edf20bed23da5a"},
    "name":"Tomato with egg",
    "recipe":"Mix tomato with egg.",
    "image":"/images/tomatowithegg.png",
    "portions":{"$numberDouble":"2.0"},
    "ingredients":
    [
        {"product_id":{"$oid":"635a715b32edf20bed23da54"},
        "serving":"tbsp",
        "amount":{"$numberDouble":"2.0"}},
        {"product_id":{"$oid":"635a71fb32edf20bed23da55"},
        "serving":"piece",
        "amount":"1"}
    ]
}

Products collection

{
    "_id":{"$oid":"635a715b32edf20bed23da54"},
    "name":"Tomato",
    "image":"/images/tomato.png",
    "nutrients":
        {
         "kcal":"300",
         "carbohydrates":"100",
         "fat":"50",
         "protein":"2"
         },
    "servings":
        {
         "g":"1",
         "piece":"200",
         "tbsp":"10",
         "tsp":"5",
         "ml":"1"
         }
}

{
    "_id":{"$oid":"635a71fb32edf20bed23da55"},
    "name":"Egg",
    "image":"/images/egg.png",
    "nutrients":
        {
         "kcal":"300",
         "carbohydrates":"100",
         "fat":"50",
         "protein":"2"
         },
    "servings":
        {
         "g":"1",
         "piece":"200",
         "tbsp":"10",
         "tsp":"5",
         "ml":"1"
         }
}

This is my current pipeline, but it results in two arrays - ingredients and products.

[{
 $lookup: {
  from: 'products',
  localField: 'ingredients.product_id',
  foreignField: '_id',
  as: 'products'
 }
}]

Could you please help me with the Lookup?

Also, would there be a way to automatically calculate nutrients for a meal, based on products inside?

Thank you,
Lukas

3

Hi @Lukasz_Filipiuk ,

If you want to replace the ingredients array with the products lookup you should use as : 'ingridiants' in the lookup this will project the ingredients output rather then have it being added .

In the next stage after the lookup you can use $group (on “$_id” ) with $sum of the elements for the nutrients sum (it will work on arrays too), however, I see that nutrients is an object with many attributes. Do you need the sum of all attribute or specific?

I want to say that if this query of showing ingredients is a popular query you should avoid the lookup and ave a schema design where full ingredients are embedded in the meal document. I don’t expect a meal to have too much of those…

Thanks
Pavel

4

Hi @Pavel_Duchovny thank you very much for your reply!

Ingredients array has 3 fields - product_id, serving and quantity. I would love to lookup products so I get my detailed products information as well as serving and quantity, which come from ingredients array. It would be great to keep it separate, as user will be able to compose meals from the list of products, so one product may be reused.

ingredients array in meals collection

ingredients:
[
 {
     product_id: "32786423876",
     serving: "piece",
     quantity: 4
 }
]

products collection

{
   {
      _id: "32786423876"
     name: "Tomato"
     nutrients:
        {
           kcal: "100",
           carbohydrates: "10",
           fat: "1",
           protein: "2"
        }
     servings:
        {
           g: "1",
           piece: "80",
           ml: "1"
        }
   }
}

When it comes to calculation, its going to be harder - I may do the calculation on the backend instead of the database query. I would like to check what servings the ingredients are (e.g. in the recipe we use 4 tomatoes). Then in servings object in products collection I have information that one piece of tomato weighs 80 grams. Based on that information I can calculate nutrients of 4 tomatoes (as nutrients object contains nutrients per 100 g). That way I could calculate total nutrients for the whole meal.

5

@Lukasz_Filipiuk ,

You can keep them separately and duplicate for embedding all together.

Are the attributes updated frequently ?

Merging data from one array with a lookup from another makes it pretty complex therefore if that is the main purpose of the application (“showing meal details and planning”), its smart to embed.

Believe me it will save you hours of coding and headache :slight_smile:

Thanks
Pavel

6

@Pavel_Duchovny

I will trust you on that and try embedding.

The attributes will not be updated very frequently (at least for now).

Purpose of the application is “showing meal detail and planning”. User will be able to create products, then from these products he will be able to compose meals. Then these meals will be used to plan meals and generate a shopping list. The idea is to have a very simple planning application.

I went with the lookup approach since it’s more SQL-like, and that’s where I come from. Embedding is something new for me, but I want to try that, if that’s more efficient.

Thanks,
Lukas

1 Like
7

@Lukasz_Filipiuk ,

Its definitely more suitable for your use case.

I suggest reading the following:

Also there is a great course free on our university site called MongoDB for sql pros:

Thanks

3 Likes
8

@Pavel_Duchovny thank you very much for these!

I still wonder how to apply this into my scenario, where I believe we have a Many-To-Many case. In my app, product may be in many meals and a meal may be made of many products.

I already did embed ingredients - in the beginning I wanted to have three collections - products, ingredients and meals. An ingredient is a product with a serving and quantity. Products to Ingredients would be one to many relationship and ingredients to meals would be one-to-one. That’s why I embedded ingredients inside meals. I still have a problem with products though.

I would love to try embedding, but I still don’t understand how can I reuse the same product in many meals.

This is my current example. I would love to end up with my products embedded inside meals, but one product can appear in many meals and meals have many products.

meal


{
   _id: ObjectId("meal1"),
   ingredients: 
   [
       {
           product_id: ObjectId("AAA"),
           serving: "piece"
           quantity: "1"
       },
       {
           product_id: ObjectId("BBB"),
           serving: "tbsp"
           quantity: "3"
       },
   ]
}

products

{
    {
       _id: ObjectId("AAA"),
      nutrients :
       {
           kcal: "100"
           carbohydrates: "50"
           fat: "10"
           protein: "2"
       },
      servings:
       {
           g: "1"
           tbsp: "10" 
           piece: "30"
           ml: "1"
       },
    },
    {
       _id: ObjectId("BBB"),
      nutrients :
       {
           kcal: "50"
           carbohydrates: "40"
           fat: "5"
           protein: "5"
       },
      servings:
       {
           g: "1"
           tbsp: "15" 
           piece: "300"
           ml: "1"
       },
    },
}

Thank you,
Lukas

9

Hi @Lukasz_Filipiuk ,

So the idea is you can keep the products collection as is and show users what kind of products there is by querying this collection.

However you can use an extended pattern reference to duplicate the needed fields when you add a product as ingredients to a specific meal , if 1000 meals have the same ingredients copy them 1000s times.

{
   _id: ObjectId("meal1"),
   ingredients: 
   [
       {
           product_id: ObjectId("AAA"),
           serving: "piece"
           quantity: "1",
           nutrients :
       {
           kcal: "100"
           carbohydrates: "50"
           fat: "10"
           protein: "2"
       },
      servings:
       {
           g: "1"
           tbsp: "10" 
           piece: "30"
           ml: "1"
       },
    },{
           product_id: ObjectId("BBB"),
           serving: "tbsp"
           quantity: "3",
      nutrients :
       {
           kcal: "50"
           carbohydrates: "40"
           fat: "5"
           protein: "5"
       },
      servings:
       {
           g: "1"
           tbsp: "15" 
           piece: "300"
           ml: "1"
       },
    },
}

   ]
}

Make sense?

Now making any logic on the suming can be done with the use of $sum, $add or $multiply fully through aggregation:

Thanks

10

@Pavel_Duchovny these suggestions are great! I will now spend time to learn and apply this in my project! Thank you!

11

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.