How to use $unwind with empty nested documents

[Ramesh Reddy]

Hi,

I have a document like below

> db.Customer.find().pretty()
{
"FirstName" : "John",
"LastName" : "Doe",
"Notes" : [
{
"CustomerId" : DBRef("Customer", 1002),
"PostDate" : ISODate("2014-02-05T00:04:45Z"),
"Comment" : "Rocks"
},
{
"CustomerId" : DBRef("Customer", 1002),
"PostDate" : ISODate("2014-02-05T04:06:45Z"),
"Comment" : "Sucks"
}
],
"_id" : 1002
}
{ "_id" : 1001, "FirstName" : "Jane", "LastName" : "Smith" }

I would like to get a result document as where $unwind kind of operation returns the array elements but also includes the empty or "Notes" documents, so in effect 

{

"FirstName" : "John",

"LastName" : "Doe",

"Notes" : 

{

"CustomerId" : DBRef("Customer", 1002),

"PostDate" : ISODate("2014-02-05T00:04:45Z"),

"Comment" : "Rocks"

},

"_id" : 1002

}

{

"FirstName" : "John",

"LastName" : "Doe",

"Notes" :

{

"CustomerId" : DBRef("Customer", 1002),

"PostDate" : ISODate("2014-02-05T04:06:45Z"),

"Comment" : "Sucks"

}

],

"_id" : 1002

}

{   "_id" : 1001, 

    "FirstName" : "Jane", 

    "LastName" : "Smith" 

}

Can anybody suggest how I can accomplish this?

Thanks

Ramesh..

[Asya Kamsky]

That's not what $unwind does - if the array doesn't exist or is empty for a document then it won't return anything for it.

I ran it on your example and got:

--
--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com
To unsubscribe from this group, send email to
mongodb-user...@googlegroups.com
See also the IRC channel -- freenode.net#mongodb
 
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Asya Kamsky]

Sorry, somehow this got sent without the result... anyway I got this:

{ "_id" : 1002, "FirstName" : "John", "LastName" : "Doe", "Notes" : { "CustomerId" : DBRef("Customer", 1002), "PostDate" : ISODate("2014-02-05T00:04:45Z"), "Comment" : "Rocks" } }

{ "_id" : 1002, "FirstName" : "John", "LastName" : "Doe", "Notes" : { "CustomerId" : DBRef("Customer", 1002), "PostDate" : ISODate("2014-02-05T04:06:45Z"), "Comment" : "Sucks" } }

[Ramesh Reddy]

Asya,

Thanks for the reply, but that is the default $unwind behavior. My need was to just show the parent document without child document when one is null or empty. I see that there is way to do this with use of "$ifNull" and substitute an empty/null with dummy data. I found that here http://stackoverflow.com/questions/13895006/unwind-empty-array

Would like to see if there any easier way than this. I do wish $unwind gave this behavior with some kind additional parameter as an option.

Ramesh..

http://teiid.org

[Asya Kamsky]

Yes, I'm familiar with the answer you found (since I wrote it :) ).

There is no simpler way at the moment, but you should maybe request it in Jira - I thought maybe there was a ticket for this, but didn't find one.  

Note that you're not substituting null with dummy data, you need to substitute it with the data that matches the actual values you want to end up with...  Can you explain what you will do in the next stage of the aggregation pipeline?  Or what your final desired result would be?  Maybe there's a different way to get there...

Asya

--

[Ramesh Reddy]

Asya,

Thanks for that answer, did not pay attention before. This is what I came up based on your suggestion in the other thread

dBCollection.aggregate(

    { "$project" : { "FirstName" : 1 , "LastName" : 1 , "__S" : { "$ifNull" : [ "$Notes" , [ { }]]}}},

    { "$unwind" : "$__S"},

    { "$project" : { "_m0" : "$FirstName" , "_m1" : "$LastName" ,"_m2" : "$__S.Comment"}}

); 

The intention is come up behavior like LEFT OUTER join. But $unwind was snubbing the whole result if child array document is empty or null, which represents the right side in my join.

Let me know if this makes a good candidate for adding a JIRA?

Ramesh..

http://teiid.org

[Asya Kamsky]

I understand the result this gives you but what I'm wondering is why you need to do a left outer join on a MongoDB collection - is the result being exported to another system?  It seems strange to have the same user multiple times with each of their notes and another user with no notes at all.

I do think this is a reasonable Jira request (and I thought maybe it already existed...) maybe as an option to $unwind...

Asya

--

[Ramesh Reddy]

Asya,

Yes, the example I gave was a dummy one. I am developing a MondoDB translator for Teiid. Teiid is a open source Data Virtualization system (http://teiid.org) from JBoss Community. Teiid can integrate data from disparate systems in real time. Few of our users are looking for MongoDB integration. BTW, Redhat also provides support for Teiid under their "Redhat JBoss Data Virtualization 6" product.

Our goal is provide a SQL/relational front to MongoDB, that does not compromise with MongoDB's document model. My efforts on this feature has been documented here https://docs.jboss.org/author/display/TEIID/MongoDB+Translator. I would love to hear back on any feed back from MongoDB users.

I will add enhancement JIRA request for the above.

Thanks again.

Ramesh..

http://teiid.org

[Ramesh Reddy]

Logged an enhancement request https://jira.mongodb.org/browse/SERVER-12685

Thanks

Ramesh..