How to process mongo documents and get field wise data in array

Question

Currently I'm hitting at a problem to process the mongodb documents and get the field wise values. For example, say mongo contains these documents:

[
    { "name": "test1", "age": 20, "gender": "male" },
    { "name": "test2", "age": 21, "gender": "female" },
    { "name": "test3", "age": 30, "gender": "male"}
]

Expected Output:

{
    "name": ["test1","test2","test3"],
    "age": [20,21,30],
    "gender": ["male","female", "male"]
}

Is it possible to retrieve data from mongo in the above format? I dont want to write some javascript functions to process this. Looking at retrieving the data by using mongo functions along with the find query.

chridam chridam · Accepted Answer · 2016-09-01T12:10:18

You'd need to use the aggregation framework to get the desired result. Run the following aggregation pipeline which filters the documents in the collection getting into the pipeline for grouping using the $match operator. This is similar to the find() query filter.

db.collection.aggregate([
    { "$match": {  "age": { "$gte": 20 } } }, // filter on users with age >= 20
    {
        "$group": {
            "_id": null,
            "name": { "$push": "$name" },
            "age": { "$push": "$age" },
            "gender": { "$push": "$gender" }
        }
    },
    {
        "$project": {
            "_id": 0,
            "name": 1,
            "age": 1,
            "gender": 1
        }
    }
])

Sample Output

{
    "name": ["test1", "test2", "test3"],
    "age": [20, 21, 30],
    "gender": ["male", "female", "male"]
}

In the above pipeline, the first pipeline step is the $match operator which is similar to SQL's WHERE clause. The above example filters incoming documents on the age field (age greater than or equal to 20).

One thing to note here is when executing a pipeline, MongoDB pipes operators into each other. "Pipe" here takes the Linux meaning: the output of an operator becomes the input of the following operator. The result of each operator is a new collection of documents. So Mongo executes the previous pipeline as follows:

collection | $match | $group | $project => result

The next pipeline stage is the $group operator. Inside the $group pipeline, you are now grouping all the filtered documents where you can specify an _id value of null to calculate accumulated values for all the input documents as a whole. Use the available accumulators to return the desired aggregation on the grouped documents. The accumulator operator $push is used in this grouping operation because it returns an array of expression values for each group.

Accumulators used in the $group stage maintain their state (e.g. totals, maximums, minimums, and related data) as documents progress through the pipeline.

To get the documents with the desired field, the $project operator which is similar to SELECT in SQL is used to rename the field names and select/deselect the fields to be returned, out of the grouped fields. If you specify 0 for a field, it will NOT be sent in the pipeline to the next operator.

How to process mongo documents and get field wise data in array

2 Answers