Aggregation and Read Optimization Examples

Aggregation and Read Optimization Examples

To achieve read optimization, design document structure/schema based on use cases. There are different schemas below to support the same results with different query complexity.

Use Case 1: display all favorite books of a person in an array.

Simple Query

Places all data under an attribute as expected outputs.

Document:

...
"favoriteBooks" : [
  "The Great Gatsby",
  "Moby Dick",
  "Journey to the West"
]
...

Query:

db.favorites.aggregate([
    {
        "$project": {
            "_id": 0,
            "name": {
                "$concat": [
                    "$firstName", " ", "$lastName"
                ]
            },
            "favoriteBooks": 1
        }
    }
])

An Array of Many Attributes

If you have other use cases, Use Case 2: for a set of known attributes, read all top favorites, and thus you have a document structure as follows:

Document:

...
"favoritesList" : [
  {
    "sport" : "Soccer",
    "music" : "Soul",
    "city" : "Singapore",
    "book" : "The Great Gatsby",
    "movie" : "One Flew Over the Cuckoo's Nest"
  },
  {
    "city" : "Bangkok",
    "book" : "Moby Dick",
    "movie" : "The Shawshank Redemption",
    "sport" : "Baseball",
    "music" : "Easy Listening"
  },
  {
    "city" : "Atlanta",
    "book" : "Journey to the West",
    "movie" : "Schindler"s List",
    "sport" : "Horse racing",
    "music" : "Blues"
  }
]
...

Below is the query to support use case 2.

{
  "favorites": {
    "$arrayElemAt": ["$favoritesList", 0]
  }
}

For Use Case 1, the query is:

db.favorites.aggregate([
    {
        "$project": {
            "name": {
                "$concat": [
                    "$firstName", " ", "$lastName"
                ]
            },
            "favoritesList.book": 1
        }
    }, {
        "$unwind": {
            "path": "$favoritesList"
        }
    }, {
        "$group": {
            "_id": {
                "id": "$_id",
                "name": "$name"
            },
            "count": {
                "$sum": 1
            },
            "books": {
                "$push": "$favoritesList.book"
            }
        }
    }, {
        "$project": {
            "_id": 0,
            "name": "$_id.name",
            "books": 1
        }
    }
])

An Array of Dynamic Attributes

Store a set of dynamic categories and each category (key) has an array of values.

Document:

...
"favoritesKVSet" : [
  {
    "key" : "sport",
    "value" : [
      "Soccer",
      "Baseball",
      "Horse racing"
    ]
  },
...
  {
    "key" : "book",
    "value" : [
      "The Great Gatsby",
      "Moby Dick",
      "Journey to the West"
    ]
  },
...
],
...

Query:

db.favorites.aggregate([
    {
        "$project": {
            "name": {
                "$concat": [
                    "$firstName", " ", "$lastName"
                ]
            },
            "favoritesKVSet": {
                "$filter": {
                    "input": "$favoritesKVSet",
                    "as": "fa",
                    "cond": {
                        "$eq": [
                            "$$fa.key", "book"
                        ]
                    }
                }
            }
        }
    }, {
        "$project": {
            "_id": 0,
            "name": 1,
            "books": "$favoritesKVSet.value"
        }
    }, {
        "$unwind": {
            "path": "$books"
        }
    }
])

Another Array of Dynamic Attributes

Store a set of dynamic categories and each category (key) only has a corresponding value.

Document:

"favoritesKVList" : [
  {
    "level" : 1,
    "categories" : [
      {
        "key" : "sport",
        "value" : "Soccer"
      },
      {
        "key" : "music",
        "value" : "Soul"
      },
      {
        "key" : "city",
        "value" : "Singapore"
      },
      {
        "value" : "The Great Gatsby",
        "key" : "book"
      },
      {
        "key" : "movie",
        "value" : "One Flew Over the Cuckoo's Nest"
      }
    ]
  },
  {
    "level" : 3,
    "categories" : [
      {
        "key" : "sport",
        "value" : "Horse racing"
      },
      {
        "key" : "music",
        "value" : "Blues"
      },
      {
        "key" : "city",
        "value" : "Atlanta"
      },
      {
        "key" : "book",
        "value" : "Journey to the West"
      },
      {
        "key" : "movie",
        "value" : "Schindler's List"
      }
    ]
  }
],

Use Case: display all favorite books of a person in an array.

Use $reduce and $concatArrays

[
    {
        '$project': {
            'name': {
                '$concat': [
                    '$firstName', ' ', '$lastName'
                ]
            }, 
            'books': {
                '$map': {
                    'input': '$favoritesKVList', 
                    'as': 'fa', 
                    'in': {
                        '$filter': {
                            'input': '$$fa.categories', 
                            'as': 'fa', 
                            'cond': {
                                '$eq': [
                                    '$$fa.key', 'book'
                                ]
                            }
                        }
                    }
                }
            }
        }
    }, {
        '$project': {
            'name': 1, 
            'books': {
                '$reduce': {
                    'input': '$books', 
                    'initialValue': [], 
                    'in': {
                        '$concatArrays': [
                            '$$value', '$$this'
                        ]
                    }
                }
            }
        }
    }, {
        '$project': {
            '_id': 0, 
            'name': 1, 
            'books': '$books.value'
        }
    }
]