[MongoDB Standard Views] #mongodb

mondodb-standard-views.md

// specify 3 options - 'viewOn', 'pipeline' and 'collation'
db.createCollection(
  'some coll name here',
  {
		"viewOn": "source coll name here",
    "pipeline": [
    	//... add some agg stages here
      // NO $out, $merge
      // NO $lookup (embedded pipeline)
    ],
    "collation": {
    	
    }
  }
)

db.createView(
  "<viewName>",
  "<source>",
  [<pipeline>],
  {
    "collation" : { <collation> }
  }
)

Aggregation Optimization

• Query filter, projection, sort, skip and limit for find() are converted to equivalent aggregation pipeline stages.

• Appends the client query to the underlying pipeline and returns the results of combined pipeline. This combined pipeline received aggregation pipeline optimizations.

• The aggregation pipeline optimizer reshapes the view aggragation pipeline stages to improve performance.

Resource Locking

db.createView() obtains an exclusive lock on the source Collection or View. All subsequent operation on the Collection or View must wait until the View creation is completed, thus releasing the lock. Normally this lock will remain for a short time.

Code example

// ******************** USING VIEW TO JOIN 2 COLLECTION ********************
db.inventory.insertMany([
   { prodId: 100, price: 20, quantity: 125 },
   { prodId: 101, price: 10, quantity: 234 },
   { prodId: 102, price: 15, quantity: 432 },
   { prodId: 103, price: 17, quantity: 320 },
])
db.orders.insertMany([
   { orderId: 201, custid: 301, prodId: 100, numPurchased: 20 },
   { orderId: 202, custid: 302, prodId: 101, numPurchased: 10 },
   { orderId: 203, custid: 303, prodId: 102, numPurchased: 5 },
   { orderId: 204, custid: 303, prodId: 103, numPurchased: 15 },
   { orderId: 205, custid: 303, prodId: 103, numPurchased: 20 },
   { orderId: 206, custid: 302, prodId: 102, numPurchased: 1 },
   { orderId: 207, custid: 302, prodId: 101, numPurchased: 5 },
   { orderId: 208, custid: 301, prodId: 100, numPurchased: 10 },
   { orderId: 209, custid: 303, prodId: 103, numPurchased: 30 },
])
db.createView(
  'sales2', // name
  'orders', // source
  [
    {
      $lookup: { 
        from: 'inventory', 
        localField: 'prodId', 	// 'source' Collection field
        foreignField: 'prodId', // 'from' Collection field
        as: 'inventoryDocs', 		// value is the array of 'from' Collection matching documents
      },
    },
    {
      $project: {
        _id: 0,
        prodId: 1,
        orderId: 1,
        numPurchased: 1,
        inventoryDocs: 1,
        price: '$inventoryDocs.price', // since inventoryDocs is an array, $inventoryDocs.price is also an array
      },
    },
    {
    	$unwind: "$price"
    }
  ]
)

// checking
db.sales2.find()

db.sales2.aggregate([
   {
      $group: {
         _id: '$prodId',
         amountSold: {
            $sum: {
               $multiply: ['$price', '$numPurchased'],
            },
         },
      },
   },
])

// ******************** USING VIEW TO TEST COLLATION ********************

db.places.insertMany([
   { _id: 1, category: 'café' },
   { _id: 2, category: 'cafe' },
   { _id: 3, category: 'cafE' },
])

db.createView(
   'placesView',
   'places',
   [
      {
         $project: {
            category: 1,
         },
      },
   ],
   {
      collation: {
         locale: 'fr',
         strength: 1,
      },
   }
)

db.placesView.countDocuments({ category: 'cafe' })

// create index with collation
db.places.createIndex( { category: 1 }, { collation: { locale: "fr" } } )

// query with collation => can use index
db.places.find({ category: 'cafe' } ).collation({ locale: 'fr' })

// query without collation => use 'simple' binary collator => can not use index
db.places.find({ category: 'cafe'}) 

// ******************** Supported methods for view *******************
.aggregate()
.countDocuments()
.distinct()

.find()
.findOne()

.createCollection()
.createView()

.getCollectionInfos()
.getCollectionNames()