CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
warvariuc
warvariuc

Reputation: 59604

How to convert an array of documents to two dimensions array

I am making a query to MongoDB

db.getCollection('user_actions').aggregate([
    {$match: {
        type: 'play_started', 
        entity_id: {$ne: null}
    }},
    {$group: {
        _id: '$entity_id', 
        view_count: {$sum: 1}
    }},
])

and getting a list of docs with two fields:

enter image description here

How can I get a list of lists with two items like

[[entity_id, view_count], [entity_id, view_count], ...]

Upvotes: 3

Views: 255

Answers (1)

Sede
Sede

Reputation: 61235

Actually there are two different way to do this, depending on your MongoDB server version.

The optimal way is in MongoDB 3.2 using the square brackets [] to directly create new array fields in the $project stage. This return an array for each group. The next stage is the another $group stage where you group your document and use the $push accumulator operator to return a two dimensional array.

db.getCollection('user_actions').aggregate([
    { "$match": {
        "type": 'play_started', 
        "entity_id": { "$ne": null }
    }},
    { "$group": {
        "_id": "$entity_id", 
        "view_count": { "$sum": 1}
    }},
    {  "$project": {
        "_id": 0, 
        "result": [ "$_id", "$view_count" ] 
    }}, 
    { "$group": { 
        "_id": null, 
        "result": { "$push": "$result" }
    }}
])

From MongoDB 2.6 and prior to 3.2 you need a different approach. In order to create your array you need to use the $map operator. Because the $map "input" field must resolves to and array you need to use $literal operator to set a literal array value to input. Of course the $cond operator here returns the "entity_id" or "view_count" accordingly to the "boolean-expression".

db.getCollection('user_actions').aggregate([
    { "$match": {
        "type": 'play_started', 
        "entity_id": { "$ne": null }
    }},
    { "$group": {
        "_id": "$entity_id", 
        "view_count": { "$sum": 1}
    }},
    { "$project": {
        "_id": 0, 
        "result": { 
            "$map": { 
                "input": { "$literal": [ "A", "B"] }, 
                "as": "el", 
                "in": { 
                    "$cond": [
                        { "$eq": [ "$$el", "A" ] }, 
                        "$_id", 
                        "$view_count"
                    ]
                }
            }
        }
    }},
    { "$group": { 
        "_id": null, 
        "result": { "$push": "$result" }
    }}
])

It worth noting that this will also work in MongoDB 2.4. If you are running MongoDB 2.2, you can use the undocumented $const operator which does the same thing.

Upvotes: 3

Related Questions