How to flatten JSON result from aggregation output

Question

Using mongo db aggregation I wrote the following NodeJs api. I got the output but not getting my expected output, so how to do this can any one help me to solve this?

app.get('/polute', function (req, res) {
    Light.aggregate([ 
        { $match: {
            CREATE_DATE: {
                $lte:new Date(),
                $gte: new Date(new Date().setDate(new 
                    Date().getDate()-120)
                )
            }
        } },
        { $group: {
            _id:{ 
                month: { $month: "$CREATE_DATE" },
                year: { $year: "$CREATE_DATE" }
            },
            avgofozone:{$avg:"$OZONE"}
        } },
        { $sort:{ "year": -1 } },
        { $project: {
            year: '$_id.year',
            avgofozone: '$avgofozone',
            month: '$_id.month',_id:0
        } }
   ], function (err, polute) {
       console.log("naresh:" +JSON.stringify(polute));
        res.json(polute);
   });
});

Actual Output:

[
    { "avgofozone" : 21.07777777777778, "year" : 2018, "month" : 2 }
    { "avgofozone" : 17.8, "year" : 2018, "month" : 3 }
    { "avgofozone" : 17.8, "year" : 2018, "month" : 1 }
]

Expected Output:

[
    { 
        "zone_type": "avgofozone", 
        "year": 2018, 
        "February": 21.07777777777778, 
        "March": 17.8, 
        "January": 17.8 
    }
]

chridam · Accepted Answer · 2018-04-17 12:46:58Z

1

With MongoDb 3.6 and newer, you can leverage the use of $arrayToObject operator and a $replaceRoot pipeline to get the desired JSON output. You would need to run the following aggregate pipeline:

const monthsEnum = {
    "_id": "year",
    "1": "January",
    "2": "February",
    "3": "March",
    "4": "April",
    "5": "May",
    "6": "June",
    "7": "July",
    "8": "August",
    "9": "September",
    "10": "October",
    "11": "November",
    "12": "December"
};

Light.aggregate([ 
    { "$match": {
        "CREATE_DATE": {
            "$lte": new Date(),
            "$gte": new Date(new Date().setDate(new Date().getDate()-120))
        }
    } },
    { "$group": {
        "_id": { 
            "month": { "$month": "$CREATE_DATE" },
            "year": { "$year": "$CREATE_DATE" }
        },
        "avgofozone": { "$avg": "$OZONE" }
    } },
    { "$group": {
        "_id": "$year",
        "avgs": {
            "$push": {
                "k": { "$substr": ["$month", 0, -1 ] },
                "v": "$avgofozone"
            }
        }
    } },
    { "$replaceRoot": { 
        "newRoot": { 
            "$mergeObjects": [ 
                { "$arrayToObject": "$avgs" }, 
                "$$ROOT" 
             ] 
        } 
    } },
    { "$project": { "avgs": 0 } }
], (err, data) => {
    console.log("naresh:" +JSON.stringify(data));
    const polute = Object.keys(data).reduce((p, c) => ({...p, monthsEnum[c]: data[c]}), {});
    res.json(polute);
})

For reshaping using the aggregate pipeline only, you can use the $let operator to map the month index to a value in an array. Consider running the following pipeline:

const MONTHS = [, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];

Light.aggregate([ 
    { "$match": {
        "CREATE_DATE": {
            "$lte": new Date(),
            "$gte": new Date(new Date().setDate(new Date().getDate()-120))
        }
    } },
    { "$group": {
        "_id": { 
            "month": { "$month": "$CREATE_DATE" },
            "year": { "$year": "$CREATE_DATE" }
        },
        "avgofozone": { "$avg": "$OZONE" }
    } },
    { "$group": {
        "_id": "$year",
        "avgs": {
            "$push": {
                "k": {
                    "$let": {
                        "vars": { "monthsList": MONTHS },
                        "in": { "$arrayElemAt": ["$$monthsList", "$month"] }
                    }
                },
                "v": "$avgofozone"
            }
        }
    } },
    { "$replaceRoot": { 
        "newRoot": { 
            "$mergeObjects": [ 
                { "$arrayToObject": "$avgs" }, 
                "$$ROOT" 
             ] 
        } 
    } },
    { "$project": { "avgs": 0 } }
], (err, data) => {
    console.log("naresh:" +JSON.stringify(data));
    res.json(data);
})

edited Apr 17, 2018 at 12:46

answered Mar 24, 2018 at 14:00

chridam

104k26 gold badges246 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

VARUN Over a year ago

@ chridam any possible total query write only aggregation please suggest me

chridam Over a year ago

@VARUN I could possibly, can you point to me the question with the full details?

VARUN Over a year ago

@ chridam your write the query abusolutly correct but same output i i will check the robomongo i did"t get the output so if you possible to write the total query in aggregation

Collectives™ on Stack Overflow

How to flatten JSON result from aggregation output

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related