How to preserve Null and Empty array response during unwind
Posted By : Vipul Pandey | 28-Jan-2018
NOSQL has unlimited advantages in terms of storing data and the structure of collection(Table in SQl) and feasibility of retriving the documents.
Since the SQL has fixed header so we need to set the values of all the fields while adding the data,but in NOSQL we don'tso wenever we are trying to retrive the data using certain
specific condition the values with null or empty are not considered in NOSQL.To populates that records as well mongoDb has added certains extra elements/conditions to get the same.
To understands this lets take an example :
db.users.insertMany([
{ _id: 1, name: 'Vipul', Manager: [3,5] },
{ _id: 2, name: 'Maneesh'},
{ _id: 3, name: 'Rijul', Manager: [] },
{ _id: 4, name: 'Pulkit', Manager: [3] },
{ _id: 5, name: 'Anil', Manager: [3] }
]);
{ "acknowledged" : true, "insertedIds" : [ 1, 2, 3, 4, 5 ] }
Here I am adding 5 enteries to a collection users with the Array (Managers) which will contains the _id's of Managersbut where the managers are not defined or exist we are leaving them blank or null To view All records :
db.users.find()
{ "_id" : 1, "name" : "Vipul", "Manager" : [ 3, 5 ] }
{ "_id" : 2, "name" : "Maneesh" }
{ "_id" : 3, "name" : "Rijul", "Manager" : [ 2 ] }
{ "_id" : 4, "name" : "Pulkit", "Manager" : [ 3 ] }
{ "_id" : 5, "name" : "Anil", "Manager" : [ 3 ] }
Now if we want to get users with the details of the managers we first need to break the array of manager and populate the same via lookup
db.users.aggregate([
{"$unwind":{path:"$Manager",preserveNullAndEmptyArrays:true}},
{$lookup:{from:"users",localfield:"Manager",foreignField:"_id",as :"mgr"}}
])
This $unwind will break the array and $lookup will search the details of managers with the _id in same collections
{ "_id" : 1, "name" : "Vipul", "Manager" : 3, "mgr" : [ { "_id" : 3, "name" : "Rijul", "Manager" : [ ] } ] }
{ "_id" : 1, "name" : "Vipul", "Manager" : 5, "mgr" : [ { "_id" : 5, "name" : "Anil", "Manager" : [ 3 ] } ] }
{ "_id" : 4, "name" : "Pulkit", "Manager" : 3, "mgr" : [ { "_id" : 3, "name" : "Rijul", "Manager" : [ ] } ] }
{ "_id" : 5, "name" : "Anil", "Manager" : 3, "mgr" : [ { "_id" : 3, "name" : "Rijul", "Manager" : [ ] } ] }
As In results we can see that the user maneesh and rijul are not in response to get the list of users whose managers are not assigned or not exists
db.users.aggregate([
{"$unwind":{path:"$Manager",preserveNullAndEmptyArrays:true}},
{$lookup:{from:"users",localfield:"Manager",foreignField:"_id",as :"mgr"}}
])
{ "_id" : 1, "name" : "Vipul", "Manager" : 3, "mgr" : [ { "_id" : 3, "name" : "Rijul", "Manager" : [ ] } ] }
{ "_id" : 1, "name" : "Vipul", "Manager" : 5, "mgr" : [ { "_id" : 5, "name" : "Anil", "Manager" : [ 3 ] } ] }
{ "_id" : 2, "name" : "Maneesh", "mgr" : [ ] }
{ "_id" : 3, "name" : "Rijul", "mgr" : [ ] }
{ "_id" : 4, "name" : "Pulkit", "Manager" : 3, "mgr" : [ { "_id" : 3, "name" : "Rijul", "Manager" : [ ] } ] }
{ "_id" : 5, "name" : "Anil", "Manager" : 3, "mgr" : [ { "_id" : 3, "name" : "Rijul", "Manager" : [ ] } ] }
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Vipul Pandey
Vipul Pandey is a good team-player & developing application on MEAN and java spring boot. Vipul loves to keep rotating fingers in his keyboard until he creates somethings inspiring.Hobbies are playing cricket ,swimming & photography.