Database Management Systems
In MongoDB, you can use the $pullAll operator to remove all instances of the specified values from an existing array.
Use $pullAll in conjunction with a method such as update(), updateOne(), or updateMany() to update the specified document/s with the change.
In MongoDB, you can use the $pull operator to remove values from an array.
Use $pull in conjunction with a method such as update() to update the specified document with the change.
Sometimes when you query a collection in MongoDB, you might not be happy with the field names. By default, the field names are simply a reflection of the field names in the actual documents.
Perhaps the field names are inconsistent, or there’s a typo. Whatever the reason, you can use the $project aggregation pipeline stage to rename a field in your query results.
In some ways, this is comparable to using an alias in SQL, as it doesn’t rename the underlying fields, it simply renames them in the query results.
Continue readingIn MongoDB, the $addToSet operator adds a value to an array unless the value is already present in the array.
It’s similar to the $push operator, except that $push adds the value even if the value is already present.
In MongoDB, you can use the $push operator to append a value to an array.
You can use various modifiers to specify the value’s position in the array, the order of the elements in the array, append multiple values, etc.
Continue readingWhen you use methods such as find() or findOne() in MongoDB, by default you get the whole document returned. And if you use projections, you can return specific key/value pairs.
But what if you only want the value?
You can extract the value of a field by appending that field’s name to your query when using findOne().
If you’re familiar with SQL, you might know about the UNION clause, which concatenates the results of two queries into a single result set. In particular, UNION ALL includes duplicates.
In MongoDB, we can use the $unionWith aggregation pipeline stage to achieve the same effect that UNION ALL produces. The $unionWith stage performs a union of two collections – it combines pipeline results from two collections into a single result set. And it includes duplicates.
The following table contains a list of the date formats that you can provide to the CONVERT() function when you convert a date/time value to a string.
These formats are provided as an optional third argument when calling the CONVERT() function. They’re provided as an integer expression that specifies how the CONVERT() function will format the date.
If you’re receiving error Msg 3902, Level 16, which reads “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION”, it’s probably because you’ve got a stray COMMIT statement.
You could be getting this due to implementing error handling, and forgetting that you’ve already committed or rolled back the transaction elsewhere in your code.
Continue readingIn MongoDB, when you create an index on a field that holds an array, it’s automatically created as a multikey index.
Multikey indexes support efficient queries against array fields.
Multikey indexes can be created for arrays that hold scalar data (e.g. strings, numbers, etc) and nested documents.
Continue reading