Is it possible to flatten MongoDB result query?

Question

I have a deeply nested collection in my MongoDB collection.

When I run the following query:

db.countries.findOne({},{'data.country.neighbor.name':1,'_id':0})

I end up with this nested result here:

{"data" : {
  "country" : [
    {
      "neighbor" : [
        {
          "name" : "Austria"
        },
        {
          "name" : "Switzerland"
        }
      ]
    },
    {
      "neighbor" : {
        "name" : "Malaysia"
      }
    },
    {
      "neighbor" : [
        {
          "name" : "Costa Rica"
        },
        {
          "name" : "Colombia"
        }
      ]
    }
  ]
}}

Now, this is what I want:

['Austria', 'Switzerland', 'Malaysia', 'Costa Rica', 'Colombia']

or this:

{'name':['Austria', 'Switzerland', 'Malaysia', 'Costa Rica', 'Colombia']}

or anything else similar... Is this possible?

RameshVel · Accepted Answer · 2012-11-08T03:34:14.060

90

You can use $project & $unwind & $group of aggregation framework to get the result closer to your requirement.

> db.countries.aggregate({$project:{a:'$data.country.neighbor.name'}},
                         {$unwind:'$a'},
                         {$unwind:'$a'},
                         {$group:{_id:'a',res:{$addToSet:'$a'}}})
  {
    "result" : [
        {
            "_id" : "a",
            "res" : [
                "Colombia",
                "Malaysia",
                "Switzerland",
                "Costa Rica",
                "Austria"
            ]
        }
    ],
    "ok" : 1
}

$unwind used twice since the name array is nested deep. And It will only work if the neighbor attribute is an array. In your example one neighbor field (Malaysia) is not an array

edited Nov 08 '12 at 03:34

answered Nov 08 '12 at 03:10

RameshVel

64,778
30
169
213

Thanks! I'm getting the following though: `{ "result" : [ ], "ok" : 1 }` :/ – Marsellus Wallace Nov 08 '12 at 03:32
Interesting. It still feels like a lot of work but I guess that I'll just have to get used to it. Thanks – Marsellus Wallace Nov 08 '12 at 14:33
11

Seeing this 5 years after the fact... Mongo - which I love - is still ****ing bizarre. – The Dembinski Mar 30 '18 at 21:53

score 13 · Answer 2 · answered Jul 18 '19 at 15:31

13

Done it much simpler way, maybe it is recent

db.countries.aggregate({$unwind:'$data.country.neighbor.name'})

answered Jul 18 '19 at 15:31

wadouk

131
1
4

score 2 · Answer 3 · answered Oct 21 '22 at 12:29

To flatten your data you can also use $reduce. Here is an example in the docs.

db.countries.aggregate([
  {
    $addFields: {
      newField: {
        $reduce: {
          input: "$data.country.neighbor.name",
          initialValue: [],
          in: { $concatArrays: ["$$value", "$$this"] }
        }
      }
    }
  }
])

score -7 · Answer 4 · answered Nov 08 '12 at 03:06

-7

It's pretty straightforward under the new aggregation framework. The $project and $unwind operation are right for the purpose.

answered Nov 08 '12 at 03:06

James Gan

6,988
4
28
36

Is it possible to flatten MongoDB result query?

4 Answers4

Linked