MongoDB 3.2: Join two arrays based on array index

Question

I would like to join two Arrays from one collection based on the array index, not a (primary) key.

The Data looks like this:

{"_id" : ObjectId("1"),
    "clicks" : {
        "cumulative" : {
            "data" : [
                3,
                7,
                8
            ]
        },
        "daily" : {
            "data" : [
                3,
                4,
                1
            ]
        }
    },
    "websiteId" : "abcdef"
    "day" : {
        "isoDate" : [
            ISODate("2016-07-07T02:00:00.000+02:00"),
            ISODate("2016-07-08T02:00:00.000+02:00"),
            ISODate("2016-07-09T02:00:00.000+02:00")
            ]}
    },
....

I would like to join day.isoDate with clicks.cumulative.data based on the index of each array. The table then should look like this:

  ObjectID   Date         Clicks
    1          2016-07-07   3
    1          2016-07-08   7
    1          2016-07-09   8

What I tried so far:

db.collection1.aggregate([  
  {$unwind: "$day.isoDate"},
  {$match: {"websiteId": "abcdef"} },
  {$group: {_id: "$day.isoDate.Value"}}
])

Does anybody have a suggestion?

Kevin Smith · Accepted Answer · 2017-05-08 21:15:01Z

1

This can be achieved by telling mongodb to store the index of the unwinded array an then use it within a projection:

db.test.aggregate([  
    {"$unwind": {
      path: "$day.isoDate",
      includeArrayIndex: "index"
    }},
    {"$project": {
        "Date" : "$day.isoDate",
        "Clicks": {
            "$arrayElemAt" : [
                "$clicks.cumulative.data",
                "$index"
            ]
        }}
    }])

This will output

{ "_id" : ObjectId("5910de2e92842f684b605965"), "Date" : ISODate("2016-07-07T00:00:00Z"), "Clicks" : 3 }
{ "_id" : ObjectId("5910de2e92842f684b605965"), "Date" : ISODate("2016-07-08T00:00:00Z"), "Clicks" : 7 }
{ "_id" : ObjectId("5910de2e92842f684b605965"), "Date" : ISODate("2016-07-09T00:00:00Z"), "Clicks" : 8 }
>

answered May 8, 2017 at 21:15

Kevin Smith

14.6k5 gold badges63 silver badges88 bronze badges

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

1 Comment

Patrik Lin Over a year ago

Works like a charm! Thank you very much.

Collectives™ on Stack Overflow

MongoDB 3.2: Join two arrays based on array index

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related