I have an "Image" collection below ( Note: labels field is an array, because 1 Image maybe have many labels)
{
"_id" : ObjectId("624d182b6a4e7f001ed5cd9f"),
"is_deleted" : false,
"labels" : [
{
"_id" : ObjectId("627a0fbc12458800209dd69c"),
"labelId" : ObjectId("624d14d76a4e7f001ed5c6ca"),
"xmin" : 0.440625,
"xmax" : 0.2953125,
"ymin" : 0.102083333333333,
"ymax" : 0.366666666666667
},
{
"_id" : ObjectId("627a0fbc12458800209dd69d"),
"labelId" : ObjectId("624d14d76a4e7f001ed5c6ca"),
"xmin" : 0.2546875,
"xmax" : 0.45,
"ymin" : 0.220833333333333,
"ymax" : 0.439583333333333
},
{
"_id" : ObjectId("627a0fbc12458800209dd69e"),
"labelId" : ObjectId("624d14d76a4e7f001ed5c6ca"),
"xmin" : 0.240625,
"xmax" : 0.48125,
"ymin" : 0.208333333333333,
"ymax" : 0.466666666666667
},
{
"_id" : ObjectId("627a0fbc12458800209dd69f"),
"labelId" : ObjectId("624d14d76a4e7f001ed5c6ca"),
"xmin" : 0.275618374558304,
"xmax" : 0.436395759717314,
"ymin" : 0.136470588235294,
"ymax" : 0.404705882352941
}
],
"img_name" : "6917328478506479617.jpg",
"img_originalname" : "frame27406302021-085850.jpg",
"img_desc" : "Cyber Sercurity multi upload",
"img_uri" : "http://localhost:8080/resources/images/2022/4/6/6917328478506479617.jpg",
"img_path" : "/resources/images/2022/4/6/6917328478506479617.jpg",
"datasetId" : ObjectId("624d14216a4e7f001ed5c459"),
"createdAt" : ISODate("2022-04-06T04:33:47.120Z"),
"updatedAt" : ISODate("2022-05-10T07:09:48.306Z"),
"__v" : 0
}
And below is "Label" collection:
{
"_id" : ObjectId("624d14d76a4e7f001ed5c6ca"),
"is_deleted" : false,
"labelName" : "Đông",
"description" : "qw",
"datasetId" : ObjectId("624d14216a4e7f001ed5c459"),
"createdAt" : ISODate("2022-04-06T04:19:35.985Z"),
"updatedAt" : ISODate("2022-04-06T04:19:35.985Z"),
"__v" : 0,
"datanoiseId" : ObjectId("6261318b23a7a590480f92e6")
}
Now I want to lookup to joined all information about the labels into an Image, so I did like that:
db.Image.aggregate([
{
$match: {
$and: [{"datasetId": ObjectId("624d14216a4e7f001ed5c459"), "is_deleted": false}]
}
},
{
$lookup: {
from: "Label",
localField: "labels.labelId",
foreignField: "_id",
as: "labelDocuments",
},
},
{ $sort : { "_id" : -1 } }
])
And the result I got after I excuted above query below:
{
"_id" : ObjectId("624d182b6a4e7f001ed5cd9f"),
"is_deleted" : false,
"labels" : [
{
"_id" : ObjectId("627a0fbc12458800209dd69c"),
"labelId" : ObjectId("624d14d76a4e7f001ed5c6ca"),
"xmin" : 0.440625,
"xmax" : 0.2953125,
"ymin" : 0.102083333333333,
"ymax" : 0.366666666666667
},
{
"_id" : ObjectId("627a0fbc12458800209dd69d"),
"labelId" : ObjectId("624d14d76a4e7f001ed5c6ca"),
"xmin" : 0.2546875,
"xmax" : 0.45,
"ymin" : 0.220833333333333,
"ymax" : 0.439583333333333
},
{
"_id" : ObjectId("627a0fbc12458800209dd69e"),
"labelId" : ObjectId("624d14d76a4e7f001ed5c6ca"),
"xmin" : 0.240625,
"xmax" : 0.48125,
"ymin" : 0.208333333333333,
"ymax" : 0.466666666666667
},
{
"_id" : ObjectId("627a0fbc12458800209dd69f"),
"labelId" : ObjectId("624d14d76a4e7f001ed5c6ca"),
"xmin" : 0.275618374558304,
"xmax" : 0.436395759717314,
"ymin" : 0.136470588235294,
"ymax" : 0.404705882352941
}
],
"img_name" : "6917328478506479617.jpg",
"img_originalname" : "frame27406302021-085850.jpg",
"img_desc" : "Cyber Sercurity multi upload",
"img_uri" : "http://localhost:8080/resources/images/2022/4/6/6917328478506479617.jpg",
"img_path" : "/resources/images/2022/4/6/6917328478506479617.jpg",
"datasetId" : ObjectId("624d14216a4e7f001ed5c459"),
"createdAt" : ISODate("2022-04-06T04:33:47.120Z"),
"updatedAt" : ISODate("2022-05-10T07:09:48.306Z"),
"__v" : 0,
"labelDocuments" : [
{
"_id" : ObjectId("624d14d76a4e7f001ed5c6ca"),
"is_deleted" : false,
"labelName" : "Đông",
"description" : "qw",
"datasetId" : ObjectId("624d14216a4e7f001ed5c459"),
"createdAt" : ISODate("2022-04-06T04:19:35.985Z"),
"updatedAt" : ISODate("2022-04-06T04:19:35.985Z"),
"__v" : 0,
"datanoiseId" : ObjectId("6261318b23a7a590480f92e6")
}
]
}
You see, the field "labels" has 4 records, but in "labelDocuments" there is only 1 record? The reason is that when assigning labels to images, I have assigned many labels (with the same labelId) to 1 image, so the lookup command can only join 1 record? So how can I make "labelDocuments in this case have 4 records same "labels" field? Please help me, thanks
CodePudding user response：
The $lookup operation can get multiple records. In your data you have only one (ObjectId("624d14d76a4e7f001ed5c6ca")), which all your labels are referring to. This is the reason you get only one document in the labelDocuments array.
If your goal is to merge the labels, you can add your query a step:
{
$addFields: {
mergedLabels: {
$map: {
input: "$labels",
as: "i",
in: {
$mergeObjects: [
"$$i",
{
$first: {
$filter: {
input: "$labelDocuments",
cond: {
$eq: [
"$$this._id",
"$$i.labelId"
]
}
}
}
}
]
}
}
}
}
},
{
$unset: ["labelDocuments", "labels"]
},
Like this example: playground 1 Which is based on this answer by @turivishal
If you want to get them in a new array, as you say, you can instead add:
{
$addFields: {
labelDocuments: {
$map: {
input: "$labels",
as: "i",
in: {
$mergeObjects: [
{},
{
$first: {
$filter: {
input: "$labelDocuments",
cond: {
$eq: [
"$$this._id",
"$$i.labelId"
]
}
}
}
}
]
}
}
}
}
},
If you want to keep your labels under the image document it is better not to $unwind as this will be a costly redundant operation, but as I said, that depends on what you want to do next.
CodePudding user response：
you should unwind first.
db.Image.aggregate([{
$match: {
$and: [{
"datasetId": ObjectId("624d14216a4e7f001ed5c459"),
"is_deleted": false
}]
}
},
{
$unwind: "$lables"
},
{
$lookup: {
from: "Label",
localField: "labels.labelId",
foreignField: "_id",
as: "labelDocuments",
},
},
{
$sort: {
"_id": -1
}
}
])