42 results found

1. $populate stage

   Please provide a $populate stage that allows to resolve single referenced documents.
   Internally it could use the combination of $lookup and $unwind:

   https://stackoverflow.com/questions/41482559/how-to-return-single-object-instead-of-array-in-lookup-of-mongodb

   Related:

   https://stackoverflow.com/questions/37793844/mongodb-how-to-resolve-dbref-on-client-side

   https://jira.mongodb.org/browse/SERVER-22497

   1 vote

   Vote Vote Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Vote

   We’ll send you updates on this idea

   0 comments  ·  Query and Aggregation Pipeline  ·  Edit…  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

2. Provide straightforward syntax for 1-to-1 joins in aggregation

   The syntax for joins that bring back multiple documents from foreign collections is very straightforward and yields exactly what one would expect, but simple joins that are bread and butter in SQL require very convoluted and expensive to run syntax.

   Consider a product database that has products, categories and reviews collections. Each product has a unique category and may have multiple reviews. Getting all reviews in an aggregation is very straightforward (top stage), but getting categories, similar to SQL, is as convoluted as it gets (bottom stage).

   db.products.aggregate(
   [
       //
       // Document aggregates naturally aggregate foreign documents
       // into the primary document.
       //
       {$lookup: {
         from: "reviews",
         localField: "_id",
         foreignField: "product_id",
         as: "reviews"
       }},
   
       //
       // A simple join that is supposed to bring a couple of fields
       // into the primary document translates into this convoluted
       // syntax just to get those two fields.
       //
       {$lookup: {
         from: "categories",
         let: {category_id: "$category"},
         pipeline: [
           {$match: {
               $expr: {$eq: ["$_id", "$$category_id"]}
           }},
           {$project: {
               _id: 0,
               cat_name: "$category",
               cat_description: "$description"
           }}
         ],
         as: "category_details"
       }},
       {$replaceRoot: {
         newRoot: {
           $mergeObjects: [
             "$$ROOT", {$arrayElemAt: ["$category_details", 0]}
           ]
         }
       }},
       {$project: {
           category_details: 0
       }}
   ])
   

   Notice that keeping category.category and category.description in products, the noSQL-way, isn't an option because updating 10000's of products when category or description change is just not a good idea any way one looks at it. Denormalization is useful in many noSQL scenarios, but this isn't one of them.

   Having some simple join syntax that would identify the joined collection as a 1-to-1 join and would bring field names and values as if they existed in the primary document, possibly aliased via a projection-like syntax, would be extremely helpful in keeping aggregations readable, keeping data reasonably normalized, and would run much faster because it would be just multiple parallel BSON append calls when constructing aggregation results documents.

   Data for the query:

   products:
   
{"_id":{"$oid":"6140e6c6dfe7b9d6e597d07e"},"name":"Product A","category":1,"price":111}
{"_id":{"$oid":"6140e6c6dfe7b9d6e597d07f"},"name":"Product B","category":2,"price":222}
{"_id":{"$oid":"6140e6c6dfe7b9d6e597d080"},"name":"Product C","category":1,"price":333}

   categories:
   
{"_id":1,"category":"Clothing","description":"All kinds of clothing"}
{"_id":2,"category":"Electronics","description":"Cool gadgets"}

   reviews:
   
{"_id":{"$oid":"6140e7cadfe7b9d6e597d08a"},"product_id":{"$oid":"6140e6c6dfe7b9d6e597d07e"},"review":"Product A is great","rating":4}
{"_id":{"$oid":"6140e813dfe7b9d6e597d090"},"product_id":{"$oid":"6140e6c6dfe7b9d6e597d07f"},"review":"This product is horrible","rating":1}
{"_id":{"$oid":"6140e8eff4ff76778b48ba88"},"product_id":{"$oid":"6140e6c6dfe7b9d6e597d07f"},"review":"This product is not the best, but usable","rating":2}


   The syntax for joins that bring back multiple documents from foreign collections is very straightforward and yields exactly what one would expect, but simple joins that are bread and butter in SQL require very convoluted and expensive to run syntax.

   Consider a product database that has products, categories and reviews collections. Each product has a unique category and may have multiple reviews. Getting all reviews in an aggregation is very straightforward (top stage), but getting categories, similar to SQL, is as convoluted as it gets (bottom stage).

   db.products.aggregate(
   [
       //
       // Document aggregates naturally aggregate foreign documents
       // into

   … more
   1 vote

   Vote Vote Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Vote

   We’ll send you updates on this idea

   3 comments  ·  Query and Aggregation Pipeline  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close