combine query output in mongodb

Question

Possible duplicate!! Please tell me if there is similar answer.

I am new to mongoDB, and I don't know the terms of this action.

I have a some data like this:

[{
    "Name" : "Python",
    "Month": new Date("2020-01-01"),
    "K-data":[{"date": new Date("2020-01-01"), "volume":1},
                {"date": new Date("2020-01-02"), "volume":2},
                {"date": new Date("2020-01-03"), "volume":3},
                {"date": new Date("2020-01-04"), "volume":4},
                {"date": new Date("2020-01-05"), "volume":5},
                ],
},
{
    "Name" : "Python",
    "Month": new Date("2020-02-01"),
    "K-data":[{"date": new Date("2020-02-01"), "volume":6},
                {"date": new Date("2020-02-02"), "volume":7},
                {"date": new Date("2020-02-03"), "volume":8},
                {"date": new Date("2020-02-04"), "volume":9},
                {"date": new Date("2020-02-05"), "volume":10},
                ],
},
{
    "Name" : "Python",
    "date": new Date("2020-03-01"),
    "K-data":[  {"date": new Date("2020-03-01"), "volume":11},
                {"date": new Date("2020-03-02"), "volume":12},
                {"date": new Date("2020-03-03"), "volume":13},
                {"date": new Date("2020-03-04"), "volume":14},
                {"date": new Date("2020-03-05"), "volume":15},
                ],
}]

What I want to get is the K-data within some date range (across different documents). For example, if I want to get the K-data between 2020-01-03 and 2020-02-03, here is the code to do that

db.stock.aggregate([
    {$match: {"K-data.date" : { $lt: new Date("2020-02-03"), $gt: new Date("2020-01-02"),}}},
    {$project: 
        {"K-data": {$filter: {
            input: '$K-data',
            as: 'kdata',
            cond: { $and: [
                {$gt: ['$$kdata.date', new Date("2020-01-02")]},
                {$lt: ['$$kdata.date', new Date("2020-02-03")]}
            ]}
        }},
        _id: 0
    }}
])

And this is the result:

{
    "K-data" : [
        {
            "date" : ISODate("2020-01-03T00:00:00Z"),
            "volume" : 3
        },
        {
            "date" : ISODate("2020-01-04T00:00:00Z"),
            "volume" : 4
        },
        {
            "date" : ISODate("2020-01-05T00:00:00Z"),
            "volume" : 5
        }
    ]
}
{
    "K-data" : [
        {
            "date" : ISODate("2020-02-01T00:00:00Z"),
            "volume" : 6
        },
        {
            "date" : ISODate("2020-02-02T00:00:00Z"),
            "volume" : 7
        }
    ]
}

The questions is: How to merge the K-data into one array Like this?

{
    "K-data" : [
        {
            "date" : ISODate("2020-01-03T00:00:00Z"),
            "volume" : 3
        },
        {
            "date" : ISODate("2020-01-04T00:00:00Z"),
            "volume" : 4
        },
        {
            "date" : ISODate("2020-01-05T00:00:00Z"),
            "volume" : 5
        },
        {
            "date" : ISODate("2020-02-01T00:00:00Z"),
            "volume" : 6
        },
        {
            "date" : ISODate("2020-02-02T00:00:00Z"),
            "volume" : 7
        }

    ]
}

Tom Slabbaert · Accepted Answer · 2020-02-09 12:29:27Z

1

You can do this in several different ways, i think the "easiest" is to $unwind and then $group.

db.stock.aggregate([
    {
        $match: {
            "K-data.date": {$lt: new Date("2020-02-03"), $gt: new Date("2020-01-02"),}
        }
    },
    {
        $project:
            {
                "K-data": {
                    $filter: {
                        input: '$K-data',
                        as: 'kdata',
                        cond: {
                            $and: [
                                {$gt: ['$$kdata.date', new Date("2020-01-02")]},
                                {$lt: ['$$kdata.date', new Date("2020-02-03")]}
                            ]
                        }
                    }
                },
                _id: 0
            }
    },
    {
        $unwind: "$K-data"
    },
    {
        $group: {
            _id: null,
            "K-data": {$push: "$K-data"}
        }
    }
])

EDIT: Performance wise i'd drop the $unwind and do this:

db.stock.aggregate([
    {
        "$group": {
            "_id": null,
            "K-data": {
                "$push": "$K-data"
            }
        }
    },
    {
        "$project": {
            "K-data": {
                $reduce: {
                    input: "$K-data",
                    initialValue: [],
                    in: {$concatArrays: ["$$value", "$$this"]}
                }
            }
        }
    }
]);

edited Feb 9, 2020 at 12:29

answered Feb 9, 2020 at 10:30

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

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

2 Comments

HARZI Over a year ago

just curious, can you come up with a performance optimized version?

Tom Slabbaert Over a year ago

I added what i believe to be most efficient.

Collectives™ on Stack Overflow

combine query output in mongodb

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related