mongodb aggregate embedded document values

Question

Im Strugling with some aggregation functions in mongodb.

Say I have some documents like this

 [
 {
    _id: "1",
    periods: [
      {
         _id: "12",
         tables: [
           {
              _id: "121",
              rows: [
                  { _id: "1211", text: "some text"},
                  { _id: "1212", text: "some other text"},
                  { _id: "1213", text: "yet another text"},

              ]
           }
         ]
      },
      {
         _id: "13",
         tables: [
           {
              _id: "131",
              rows: [
                  { _id: "1311", text: "different text"},
                  { _id: "1312", text: "Oh yeah"}                      
              ]
           }
         ]
      }
    ]
 },
 {
    _id: "2",
    periods: [
      {
         _id: "21",
         tables: [
           {
              _id: "212",
              rows: [
                  { _id: "2121", text: "period2 text"},
                  { _id: "2122", text: "period2 other text"},
                  { _id: "2123", text: "period2 yet another text"},

              ]
           }
         ]
      }
    ]
 }
 ]

Now I want to use a mongodb query to retrieve all unique texts for one specific top level item.

eg agregate all texts for the top _id 1. This would mean that I want to get all the texts in both of the period subtrees.

expected output would be as follows:

aggregate texts filtering on _id: 1

[
   "some text",
   "some other text",
   "yet another text",
   "different text",
   "Oh yeah"
]

aggregate texts filtering on _id: 2

[
  "period2 some text",
  "period2 some other text",
  "period2 yet another text"
]

So far I have managed to aggregate all the texts , but the end up in multiple arrays and I have not managed to filter them on the id using $match,

My current aggregate query looks like this

[ 
    { "$project" : { "text" : "$periods.tables.rows.text" , "_id" : "$_id"}},
    { "$unwind" : "$text"},
    { "$group" : { "_id" : "$_id" , "texts" : { "$addToSet" : "$text"}}},
    { "$project" : { "_id" : 0 , "texts" : 1}} 
]

It gives me a result loooking something like this

{ "texts" : [ 
        [ [ "Some text" , "Some other text" , "yet another text"] , [ "different text" , "oh yeah" ] ],
        [ [ "period2 some text", "period2 some other text", "period2 yet another text"]]
    ]}

If I add $match: {_id: 1}, no results are returned.

Can anyone please help me out with this one, or point me in a direction on how to solve it. I've been searching for resources, but doesnt seem to find any good documentation on how to use these aggregate functions. The mongodb docs only use simple documents.

PS I know I can do this using mapreduce, but was hoping to be able to use an aggregate function for this.

Answer 1

Unwind only goes down one level, so you have to call as many times as many levels you have if you do it like

[ 
    { "$project" : { "text" : "$periods.tables.rows.text" , "_id" : "$_id"}},
    { "$unwind" : "$text"},
    { "$unwind" : "$text"},
    { "$unwind" : "$text"},
    { "$group" : { "_id" : "$_id" , "texts" : { "$addToSet" : "$text"}}},
    { "$project" : { "_id" : 0 , "texts" : 1}} 
]

It will work as you expect.