Lookup aggregation issue

Working with Data
,
1

Hello, I use a pipeline to get some data from collection A, the document Da in collection A looks like:

{
   _id : "AAA",
   status: "active",
   desc: "the desc of AAA",
   creation_date: "2019-05-11T10:59:55.627+00:00"
}

At the end of the pipeline there is a $lookup stage, it is:

bson.D{{
   "$lookup", bson.D{
      {"from", "alias"},
      {"localField", "_id"},
      {"foreignField", "place_id"},
      {"as", "aliases"}
   }
}}

The document in collection “alias” looks like

{
   _id: ObjectId("605af6b150d88dccc7bbadd8"),
   place_id: "AAA",
   content: "it is the alias of AAA"
}

The returned document from the pipeline should be:

{
   _id : "AAA",
   status: "active",
   desc: "the desc of AAA",
   creation_date: "2019-05-11T10:59:55.627+00:00",
   aliases: ["it is the alias of AAA"]
}

The issue is: the aliases field is not in the result, only document Da is returned. It seems the $lookup stage not work, but all stages ahead of $lookup stage works fine, otherwise Da will not be returned.

But, if I manually add an “aliases” field in the document Da in collection A, even the “aliases” is empty, i.e. if I change Da to:

{
   _id : "AAA",
   status: "active",
   desc: "the desc of AAA",
   creation_date: "2019-05-11T10:59:55.627+00:00",
   aliases:""
}

then if I execute the same pipeline, with the ending stage is the $lookup, the return result of the pipeline will be:

{
   _id : "AAA",
   status: "active",
   desc: "the desc of AAA",
   creation_date: "2019-05-11T10:59:55.627+00:00",
   aliases: ["it is the alias of AAA"]
}

in other words, the pipeline running correctly. the $lookup seems worked.
I don’t know why such issue happened, thanks for the help.

2

Given the sample documents you provided and the pipeline you provided you should not get the following as result:

It should be (as tested in the shell):

{
	"_id" : "AAA",
	"status" : "active",
	"desc" : "the desc of AAA",
	"creation_date" : "2019-05-11T10:59:55.627+00:00",
	"aliases" : [
		{
			"_id" : ObjectId("605af6b150d88dccc7bbadd8"),
			"place_id" : "AAA",
			"content" : "it is the alias of AAA"
		}
	]
}

So there is some other manipulations you are doing (to get only the string in the array) that you are not telling us about and I suspect the problem lies there. In the shell I get exactly the same result whether a field aliases exist or not in the A collection.

Please provide the whole pipeline.

3

Hello Steeve, thanks for the support. The whole document Da in collection A is

{
   _id : "AAA",
   status: "active",
   desc: "the desc of AAA",
   creation_date: "2019-05-11T10:59:55.627+00:00",
   location : {
       type : "Point",
       coordinates : [ 5.90375, 10.2892]
}

The stage before the lookup is a geoNear:

func createGeoStage(lng float32, lat float32, radius int32) (jsonStage string) {

	geoStage := `
	{
		"$geoNear":{
			"includeLocs":"location",
			"distanceField":"distance",
			"near":{
				"type":"Point",
				"coordinates":[ %f, %f]
			},
			"maxDistance": %v,
			"spherical":true
		}
	}`
	geoStage = fmt.Sprintf(geoStage, lng, lat, radius)
	return geoStage
}

By setting the location as [5.9037, 10.289] and radius = 2000, I can create the geonear stage and return Da.
The lookup stage is

bson.D{{
   "$lookup", bson.D{
      {"from", "alias"},
      {"localField", "_id"},
      {"foreignField", "place_id"},
      {"as", "aliases"}
   }
}}

The full pipeline printed by the logging:

"searching":[[{"Key":"$geoNear","Value":[{"Key":"includeLocs","Value":"location"},{"Key":"distanceField","Value":"distance"},{"Key":"near","Value":[{"Key":"type","Value":"Point"},{"Key":"coordinates","Value":[5.9037,10.289]}]},{"Key":"maxDistance","Value":2000},{"Key":"spherical","Value":true}]}],[{"Key":"$lookup","Value":[{"Key":"from","Value":"alias"},{"Key":"localField","Value":"_id"},{"Key":"foreignField","Value":"place_id"},{"Key":"as","Value":"aliases"}]}]],

I am using golang driver and the way I call the Aggregate is:

	cur, err := placeColl.Aggregate(context.TODO(), pipeline, options.Aggregate()) // use the default options of aggregate

There are $limit and $skip operators in the pipeline, but I think they will not impact the result array.

4

Hello again, there is a view on Collection place, the view is created by the pipeline below:

	lookupStage := bson.M{
		"$lookup": bson.M{
			"from": "alias",
			"let":  bson.M{"placeID": "$_id"},
			"pipeline": []bson.M{
				bson.M{
					"$match": bson.M{
						"$expr": bson.M{"$eq": []string{"$place_id", "$$placeID"}},
					},
				},
				bson.M{
					"$project": bson.M{"content": 1, "_id": 0},
				},
			},
			"as": "aliases",
		},
	}

But when I run the pipeline with geoNear directly on the view, I get error said “$geoNear should be the first stage of the pipeline”. Then I change to run the $genNear on the collection A, in the mean time I add a $lookup stage after the $genNear to join search some content in alias collection.
It seems when I run the pipeline on collection, it use some the result from its view, because in the view creation process the “content” field in alias document is kept as “aliases” in the document Da. But if there is no “aliases” field, in the collection A, the pipeline can’t get the content by the lookup stage.
What I can confirm is I did run the pipeline on collection, not on the view.

5

This is the manipulation I was writing about:

To be honest, now I am lost. That is why we prefer to have exact collections names, real documents. When things are redacted, some times, they are not redacted consistently and we are lost.

Can you confirm the exact names of your collections and views? You started with A but now it seems that it is named places or something like that.

6

Hello, the Collection A is “place” collection, and there is another collection “alias”, and on the “place” collection I create a view “place_view_with_alias” which is created by a pipeline with $lookup operator. The $lookup will find docs in the “alias” collection from the “place” collection, and $project the “content” to be “aliases” in the view “place_view_with_alias”. I confirm I use $project operator to scissor the content. But when I run the pipeline (geoNear + lookup) on the “place” collection, in the $lookup stage there is no $project operator as you have mentioned. I can confirm that the $project operator is only used in the pipeline creating the view. My problem is when I using the (geoNear+lookup) pipeline on the “place” collection, it return the docs from the view “place_view_with_alias” (if I add “aliases” field in “place” collection), or return doc without anything from alias (if there is no “aliases” in place collection).

7

As I am not familiar with golang, I transformed your pipeline into a shell query that gave me:

{
	"$lookup" : {
		"from" : "alias",
		"let" : {
			"placeID" : "$_id"
		},
		"pipeline" : [
			{
				"$match" : {
					"$expr" : {
						"$eq" : [
							"$place_id",
							"$$placeID"
						]
					}
				}
			},
			{
				"$project" : {
					"content" : 1,
					"_id" : 0
				}
			}
		],
		"as" : "aliases"
	}
}

and that almost work as I get:

{
	"_id" : "AAA",
	"status" : "active",
	"desc" : "the desc of AAA",
	"creation_date" : "2019-05-11T10:59:55.627+00:00",
	"aliases" : [
		{
			"content" : "it is the alias of AAA"
		}
	]
}

I am too unfamiliar with golang and I don’t think I can help further. Hopefully, a more skilful goland user can.

8

@steeve, Hello Steeve, thank you again for the kindly help.