MariaDB has a GROUP_CONCAT() function that enables us to return columns from a query as a delimited list.
Tag: aggregation
STRING_AGG() Function in PostgreSQL
In PostgreSQL, we can use the STRING_AGG() function to return columns from a query as a delimited list.
Remove Duplicates when using $unionWith in MongoDB
In MongoDB, the $unionWith aggregation pipeline stage performs a union of two collections, and it includes duplicates.
This behaves in a similar way to SQL’s UNION ALL, which also includes duplicates. By contrast, using just UNION (i.e. without the ALL)in SQL removes duplicates.
In MongoDB, we don’t have the option of specifying $unionWith ALL or similar, so we need to reduce duplicates in another way.
In MongoDB, we can remove duplicates by using the $group stage.
MongoDB Aggregation Operators for Returning Date Parts
This article lists the various aggregation pipeline operators that can be used to return individual date parts from a Date object in MongoDB.
Continue readingMongoDB $round vs $trunc: What’s the Difference?
MongoDB’s aggregation pipeline framework includes a $round operator and a $trunc operator. These operators perform similar, but different tasks.
$strLenBytes vs $strLenCP in MongoDB: What’s the Difference?
MongoDB includes the $strLenBytes and $strLenCP operators in its aggregation pipeline framework. These operators do a similar but slightly different thing. In some cases, both will return exactly the same result, while in other cases the results will differ.
Here’s a quick overview of the difference between these two operators.
Continue reading2 Ways to Get a Document’s Size in MongoDB
If you need to return the size of a document in MongoDB, you can use the following:
- The
$bsonSizeaggregation pipeline operator - The
Object.bsonSize()method
Below are examples of each approach.
Continue readingHow to Format the Date in MongoDB
If you have documents that store dates as Date objects, but you want to return them in a different format, you can use the $dateToString aggregate pipeline operator.
For example, you might want a date to be returned in mm/dd/yyyy format instead of the long ISODate() format that includes minutes, seconds, milliseconds, etc
The $dateToString operator converts the Date object to a string, and optionally allows you to specify a format for the resulting output.
MongoDB $sort
In MongoDB, the $sort aggregation pipeline stage sorts all input documents and returns them to the pipeline in sorted order.
MongoDB $switch
In MongoDB, the $switch aggregation pipeline operator evaluates a series of case expressions, and executes a specified expression only when a case expression evaluates to true.