2

This is the example of 1: N relationship. There is a one root item which consists of the few items (children):

{
    "_id" : ObjectId("52d017d4b60fb046cdaf4851"),
    "dates" : [
        1399518702000,
        1399126333000,
        1399209192000,
        1399027545000
    ],
    "dress_number" : "4",
    "name" : "J. Evans",
    "numbers" : [
        "5982",
        "5983",
        "5984",
        "5985"
    ],
    "goals": [
        "1",
        "0",
        "4",
        "2"
    ],
   "durations": [
       "78",
       "45",
       "90",
       "90"
   ]
}

What I want to do is to show children data from the root item:

{
    "dates": "1399518702000",
    "numbers": "5982",
    "goals": "1",
    "durations: "78"
},
{
    "dates": "1399126333000",
    "numbers": "5983",
    "goals": "0",
    "durations": "45"
},
{
    "dates": "1399209192000",
    "numbers": "5984",
    "goals": "4",
    "durations": "90"
},
{
    "dates": "1399027545000",
    "numbers": "5985",
    "goals": "2",
    "durations": "90"
}

In the table structure would look like:

Root item:

  name      number
J. Evans      4 

Children items

 dates           numbers        goals           durations
 1399518702000        5982            1             78
 1399126333000        5983            0             45
 1399209192000        5984            4             90
 1399027545000        5985            2             90

I'm trying to perform this situation using $unwind operator:

db.coll.aggregate([{ $unwind: "dates" }, { $unwind: "numbers" }, { $unwind: "goals" }, { $unwind: "durations"} ])

but query doesn't give expected data :/ Here is the great solution, but works with only two arrays.

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
corry
  • 1,457
  • 7
  • 32
  • 63

1 Answers1

8

The following pipeline should give you the idea

db.getCollection('yourCollection').aggregate(
    {
        $unwind: {
            path: "$dates",
            includeArrayIndex: "idx"
        }
    },
    {
        $project: {
            _id: 0,
            dates: 1,
            numbers: { $arrayElemAt: ["$numbers", "$idx"] },
            goals: { $arrayElemAt: ["$goals", "$idx"] },
            durations: { $arrayElemAt: ["$durations", "$idx"] }
        }
    }
)

which produces the following output for your sample

/* 1 */
{
    "dates" : NumberLong(1399518702000),
    "numbers" : "5982",
    "goals" : "1",
    "durations" : "78"
}

/* 2 */
{
    "dates" : NumberLong(1399126333000),
    "numbers" : "5983",
    "goals" : "0",
    "durations" : "45"
}

/* 3 */
{
    "dates" : NumberLong(1399209192000),
    "numbers" : "5984",
    "goals" : "4",
    "durations" : "90"
}

/* 4 */
{
    "dates" : NumberLong(1399027545000),
    "numbers" : "5985",
    "goals" : "2",
    "durations" : "90"
}
DAXaholic
  • 33,312
  • 6
  • 76
  • 74