Using document (nested object/array) storage vs flat collections/SQL-relational... Approach?

MongoDB for Startups
#1

We’re coming from the relational SQL world and are curious about how to shape nested data.

Assume we have a collection, with each document with multiple properties and some of those properties are arrays of lots and lots of other documents… How do we create a query that will give us a clean subset of those properties and subsets (potentially ordered/filtered) of the arrays of other documents?

Any thoughts/ideas? Newbie here.

#3

Hello @Christopher_Eaton ,

Welcome to The MongoDB Community Forums! :wave:

You can take advantage of The MongoDB Aggregation Framework which consists of one or more stages that process documents. Please refer below links to learn more about Aggregation Operations

For example, I have added below documents to my collection named products

db.products.insertMany([{
  "name": "Product A",
  "price": 100,
  "reviews": [
    {
      "rating": 5,
      "comment": "This product is great!"
    },
    {
      "rating": 6,
      "comment": "Best product in market!"
    },
    {
      "rating": 4,
      "comment": "I like this product."
    }
  ]
},
{
  "name": "Product B",
  "price": 200,
  "reviews": [
    {
      "rating": 5,
      "comment": "This product is Best!"
    },
    {
      "rating": 3,
      "comment": "This product is okay."
    },
    {
      "rating": 4,
      "comment": "I don't like this product."
    }
  ]
},
{
  "name": "Product C",
  "price": 200,
  "reviews": [
    {
      "rating": 2,
      "comment": "This product is Bad!"
    },
    {
      "rating": 3,
      "comment": "This product is not okay."
    },
    {
      "rating": 4,
      "comment": "I like this product."
    }
  ]
}])

Now, suppose I want to get a list of all products with

  • an average review rating of 4 or higher.
  • Output should include name, average_rating and reviews.
  • Output should have a sorted list based on average rating in descending order.

We can use below query to achieve above mentioned requirements

db.products.aggregate([
  { "$unwind": "$reviews" },
  { "$group": {
    "_id": "$name",
    "average_rating": { "$avg": "$reviews.rating" },
    "reviews": { "$push": "$reviews.comment" }
  }},
  { "$match": {
    "average_rating": { "$gte": 4 }
  }},
  { "$sort": {
    "average_rating": -1
  }}
])

Output will be

{
  _id: 'Product A',
  average_rating: 5,
  reviews: [
    'This product is great!',
    'Best product in market!',
    'I like this product.'
  ]
}
{
  _id: 'Product B',
  average_rating: 4,
  reviews: [
    'This product is Best!',
    'This product is okay.',
    "I don't like this product."
  ]
}

For the above example, I used the following aggregation stages:

  • $unwind - Deconstructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.
  • $group - separates documents into groups according to a “group key”. The output is one document for each unique group key.
  • $avg - Returns the average value of the numeric values. $avg ignores non-numeric values.
  • $push - appends a specified value to an array.
  • $match - Filters the documents to pass only the documents that match the specified condition(s) to the next pipeline stage.
  • $gte - selects the documents where the value of the field is greater than or equal to (i.e. >= ) a specified value.
  • $sort - Sorts all input documents and returns them to the pipeline in sorted order.

Notes: If you run the test by yourself, try running each stage one by one and check the output, that will help you understand the working of each stage with results. Also, this query involves a very few but most common operations and there are many more things that can be done, please refer Operators. All this computation/manipulation is done at database end so no need to worry about the data manipulation at Applicaiton layer.

Lastly, MongoDB is document database meaning that it will match documents like an SQL query matches a row. Each document may consist of many Key-value pairs with value as any data type. This data type can be an array which comes under Embedding schema design. There are several advantages and a few limitations to any schema design. I would recommend you to refer below Schema design links which will help you understand how to design a schema as per your application requirements.

To learn more, please visit MongoDB University which provides free MongoDB courses.

Regards,
Tarun

2 Likes