How to get all fields of projection, if they doesn't exist in the collection?

Hey, I want to show all the document fields in the output but some of the fields are not present in some of the document then how can i display those fields with “NULL”. Here is the example:
Sample document:

/* 1 */
{
    "_id" : "1",
    "d" : 4.5,
    "c" : 1.1,
    "b" : "Nothing Special",
    "a" : false
} 
/* 2 */
{
    "_id" : "2",
    "a" : true
} 

Required Output:

{
        "_id" : "1",
        "d" : 4.5,
        "c" : 1.1,
        "b" : "Nothing Special",
        "a" : false
}
{
        "_id" : "2",
        "d" : null,
        "c" : null,
        "b" : null,
        "a" : true
}

Thanks in advance.

1 Like

The below query will help to get the desired output by using $ifNull.

db.getCollection("abcdef").aggregate(
    [
        
         { 
            "$project" : { 
                
                 "_id":1,
                 "a": { $ifNull: [ "$a", null ] },                                                     
                 "b":{ $ifNull: [ "$b", null ] },                                                     
                 "c":{ $ifNull: [ "$c", null ] },                                                     
                 "d":{ $ifNull: [ "$d", null ] }
                 
                 }	 
        }

    ]
);
3 Likes

what if the data contained some array. For example:

/* 1 */
{
  "_id": "1",
  "d": [{ "element1": 1.1 }, { "element2": 1.2 }],
  "c": [{ "element1": 2.1 }],
  "b": [{ "element1": 3.1 }, { "element2": 3.1 }],
  "a": false
}
/* 2 */
{
    "_id" : "2",
    "a" : [{ "element1": 1.1.2 }]
} 

Required Output:
{
“_id” : “1”,
“d”: [{ “element1”: 1.1 }, { “element2”: 1.2 }],
“c”: [{ “element1”: 2.1 }, { “element2”: null }],
“b”: [{ “element1”: 3.1 }, { “element2”: 3.1 }],
“a”: false
}
{
“_id” : “2”,
“d” : [{ “element1”: null }, { “element2”: null }],
“c” : [{ “element1”: null }, { “element2”: null }],
“b” : [{ “element1”: null }, { “element2”: null }],
“a” : [{ “element1”: 1.1.2 }, { “element2”: null }]
}

How would the query look like?

Thanks for the help.

Simply do exactly the same king of $project with $ifNull but for each value you want null.

It will get complex and ugly very fast as you will need to use $range to create you array indexes, $map to map each element to the existing element or to your default null using $ifNull.

But, my personal opinion, is that this king of null-ishing cosmetic manipulations are better done on the application data access layer rather than the data server. It is easier to scale the state-less data access layer rather than the server. And most of the time there is nothing to do on the application side, you just access the data and you get null or undefined in most languages. You absolutely gain nothing by having the data server do more work in order to send more useless data over the wire. This is reminiscence of SQL where all columns are there even when there is no data.