Aggregate query to fetch data which does not have blank("") or null value is not working properly

Question

I have this aggregate query to fetch data which does not have blank("") value or null value in my collection. Here is my query :

MyCollectionA.aggregate([
    {$lookup: { "from" : "MyCollectionB","localField" : "MyCollectionAId", "foreignField" : "_id", "as" : "myData"}},
    {$match: {"myData": {$nin: [null, "", " "]}}},
    {$group: {'_id': '$MyCollectionAId'}}]);

This updated query resolved my problems.

MyCollectionA data:

{ 
    "_id" : "AbqMZXWt4kwFbJM8Y", 
    "MyCollectionBId" : "DEW7QiEA5wbaFkKkJ", 
    "MyCollectionAName" : "asdfasdf"
}
{ 
    "_id" : "Ac2CJbz6o4xKQETAZ", 
    "MyCollectionBId" : "5JjzxkxfBsNXsWcrz", 
    "MyCollectionAName" : "asdfasdfads."
}
{ 
    "_id" : "AcE2WDqbRG9dv3Lsc", 
    "MyCollectionBId" : "5678179e20a3d410709ba7f4", 
    "MyCollectionAName" : "asfasfdasdf"
}
{ 
    "_id" : "AcXsSNc6jmmDyEF54", 
    "MyCollectionBId" : "2Cy9TpzeqwkTibLy9", 
    "MyCollectionAName" : "asdfasdfadsr"
}

MyCollectionB data:

{ 
    "_id" : "5JjzxkxfBsNXsWcrz", 
    "myField" : " "
}
{ 
    "_id" : "5678179e20a3d410709ba7f4", 
    "myField" : null
}
{ 
    "_id" : "2Cy9TpzeqwkTibLy9", 
    "myField" : "asdf"
}

But this query gives me output with data which has blank and null value.

Expected result would be :

{ 
    "_id" : "2Cy9TpzeqwkTibLy9", 
    "MyCollectionBId" : "5JjzxkxfBsNXsWcrz",
    "myField" : "asdf"
}

Can anyone have idea about this?

There's no field with the key `myField` in `MyCollectionA` as specified in the `$lookup` operator's `"localField" : "myField"` option. Perhaps you meant `"localField" : "MyCollectionAId"`? Also, can you show use your expected output from the above aggregation? — chridam, Oct 10 '16 at 12:04
If it's from `MyCollectionB` why then is it specified as the `localField` for `MyCollectionA` in your `$lookup` operator parameters? — chridam, Oct 10 '16 at 12:32
@chridam because i want to find data where "myField" should not have bank or null data. — Hitesh S, Oct 10 '16 at 12:36
I get that but it seems your `$lookup` pipeline is wrong as from the [**docs**](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/) `localField` should be a field from the input documents, where in your example above the inputs are coming from `MyCollectionA` and the value you specified for that parameter is not from `MyCollectionA`. Further, If an input document does not contain the `localField`, the `$lookup` treats the field as having a value of `null` for matching purposes. — chridam, Oct 10 '16 at 12:42
@chridam i have collection MyCollectionA which has data in which i have id of MyCollectionB and in MyCollectionB i have that blank data which i don't want. — Hitesh S, Oct 10 '16 at 12:45
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/125335/discussion-between-meteor-and-chridam). — Hitesh S, Oct 10 '16 at 12:46
Please add your expected results to the question. anyways, I guess you meant to want to filter all results where `myField` from collection B is empty, right? what about if there is no match in the `lookup`? you have to be more clear and specific. expected results would be usefull — TomG, Oct 10 '16 at 13:55
@Tom Yes. If there is no match then it should not give that result. Like this $match{"myData": { $ne: [] }} — Hitesh S, Oct 11 '16 at 03:59
I still need expected results so I can see what you want to project and filter — TomG, Oct 11 '16 at 06:45

score 0 · Answer 1 · answered Oct 12 '16 at 06:25

Use localField as a field in your collection from where you are calling $lookup. In this case, the field is 'myCollectionBId'.

Also, after the lookup stage, the matching documents from the foreign collection will be appended as an array to the field which is mentioned in the 'as' field in your $lookup stage. So you cannot directly query on 'myData'. You have to use the '$match' operator on 'myData.0.myField'.

db.MyCollectionA.aggregate([

{$lookup: { "from" : "MyCollectionB", "localField" : "MyCollectionBId", "foreignField" : "_id", "as" : "myData"}},

{$match :  { "myData":{$exists:true}, "myData.0.myField": {$nin: [null, "", " "]}}},

{$project: { "MyCollectionBId":1, "myField":"myData.0.myField"}}

]);

Aggregate query to fetch data which does not have blank("") or null value is not working properly

1 Answers1