Expressive Query Operator

• $expr allows us to use variables and conditional statements
• $expr also allows the use of aggregation expressions within the query language
• Can we compare fields within the same document to each other?

db.collection_name.find({"$expr": {<expression>}})

• For example, let's say from the sample_training.trips collection we need to find all the customers who started a trip and returned the bicycle at the same station.

db.trips.find({"$expr": {"$eq": ["$start station id", "$end station id"]}}).pretty()

• Another example, let's find all the customers who came back to where they started from and the trip duration being longer than two minutes

db.trips.find({
	"$expr": {
		"$and": [
			{"$gt": ["$tripduration": 1200]},
			{"$eq": ["$start station id": "$end station id"]}
		]
	}
})

Practice Question

Find how many companies in the sample_training.companies collection have their permalink similar to their twitter username

db.companies.find({"$expr": {"$eq": ["$permalink", "$twitter_username"]}}).count()

Array Operators

• MQL offers various operators to query array fields in a document.
• $push this operator adds an element in the array or turns a field into an array field if it was previously a different type
• $all this operator returns all the documents that have at least those values in the specified array
• $size this when included only return the documents that has exactly the same number of elements in the array field.
• For example, from the sample_airbnb.listingAndReviews collection, we want to find all the documents that has Shampoo included in the amenities