I have 3 kinds of document listed as below:
vilya_be> db.plots.find({})
[
{
_id: ObjectId("6426b069ca83da130cdb7f70"),
x: 0,
y: 0,
_class: 'com.vilya.farm.domain.model.Plot'
}
]
vilya_be> db.users_have_plots.find({})
[
{
_id: ObjectId("6426b073ca83da130cdb7f71"),
userId: '6412c76956d4170a7de34d92',
plot: DBRef("plots", ObjectId("6426b069ca83da130cdb7f70")),
_class: 'com.vilya.farm.domain.model.UserPlot'
}
]
vilya_be> db.users.find({})
[
{
_id: ObjectId("6412c76956d4170a7de34d92"),
email: '[email protected]',
password: '$2a$10$s9VgOYd.fOKZF66TnAsjWemiCYkA7aG45NJpuSNgbVxpcIGF7fWqu',
firstName: 'f',
lastName: 'l',
plots: [ DBRef("users_have_plots", ObjectId("6426b073ca83da130cdb7f71")) ],
_class: 'com.vilya.farm.domain.model.User'
},
{
_id: ObjectId("6414667360e4ba4481052627"),
email: '[email protected]',
password: '$2a$10$OP52phZ61l2JX2e2TQOu9ubYFBYcPeqEZ92ox2Nyyp5e.MEZk7GhS',
firstName: 'f',
lastName: 'l',
_class: 'com.vilya.farm.domain.model.User'
}
]
I want to load all users
along with their users_have_plots
(along with their plots
) on a single command.
I have tried:
db.users.aggregate([
{
"$lookup": {
"from": "users_have_plots",
"let": {
"plots": "$plots"
},
"pipeline": [
{
"$match": {
"$expr": {
"$in": [
"$_id",
"$$plots"
]
}
}
},
{
"$lookup": {
"from": "plots",
"localField": "plot",
"foreignField": "_id",
"as": "plot"
}
}
],
"as": "plots"
}
}
]);
It gives me: PlanExecutor error during aggregation :: caused by :: $in requires an array as a second argument, found: missing
And this:
db.users.aggregate([
{
"$lookup": {
"from": "users_have_plots",
"let": {
"plots": "$plots"
},
"pipeline": [
{
"$match": {
"$expr": {
"$in": [
"$_id",
"$$plots.ObjectId"
]
}
}
},
{
"$lookup": {
"from": "plots",
"localField": "plot",
"foreignField": "_id",
"as": "plot"
}
}
],
"as": "plots"
}
}
]);
It also gives me: PlanExecutor error during aggregation :: caused by :: $in requires an array as a second argument, found: missing
I'm new to mongodb and just cannot find anyway to make it works. Any help will be appreciate!
mongo version: mongo:6.0.2-focal running on docker desktop
EDIT: Tried:
db.users.aggregate([{
"$lookup": {
"from": "users_have_plots",
"let": {
"plots": "$plots"
},
"pipeline": [
{
"$match": {
"$expr": {
"$in": [
"$_id",
{
"$ifNull": [
"$$plots.ObjectId",
[]
]
}
]
}
}
},
{
"$lookup": {
"from": "plots",
"localField": "plot",
"foreignField": "_id",
"as": "plot"
}
}
],
"as": "plots"
}
}
])
Giving me:
[
{
_id: ObjectId("6412c76956d4170a7de34d92"),
email: '[email protected]',
password: '$2a$10$s9VgOYd.fOKZF66TnAsjWemiCYkA7aG45NJpuSNgbVxpcIGF7fWqu',
firstName: 'f',
lastName: 'l',
plots: [],
_class: 'com.vilya.farm.domain.model.User'
},
{
_id: ObjectId("6414667360e4ba4481052627"),
email: '[email protected]',
password: '$2a$10$OP52phZ61l2JX2e2TQOu9ubYFBYcPeqEZ92ox2Nyyp5e.MEZk7GhS',
firstName: 'f',
lastName: 'l',
_class: 'com.vilya.farm.domain.model.User',
plots: []
}
]
No plots included.
I think you are using Dbrefs
as defined here in the documentation. That's why your queries are not working, because DBRef
store the documents in this format, internally:
db={
"plots": [
{
_id: ObjectId("6426b069ca83da130cdb7f70"),
x: 0,
y: 0,
_class: "com.vilya.farm.domain.model.Plot"
}
],
"users_have_plots": [
{
_id: ObjectId("6426b073ca83da130cdb7f71"),
userId: "6412c76956d4170a7de34d92",
plot: {
"$ref": "plots",
"$id": ObjectId("6426b069ca83da130cdb7f70")
},
_class: "com.vilya.farm.domain.model.UserPlot"
}
],
"users": [
{
_id: ObjectId("6412c76956d4170a7de34d92"),
email: "[email protected]",
password: "$2a$10$s9VgOYd.fOKZF66TnAsjWemiCYkA7aG45NJpuSNgbVxpcIGF7fWqu",
firstName: "f",
lastName: "l",
plots: [
{
"$ref": "users_have_plots",
"$id": ObjectId("6426b073ca83da130cdb7f71")
}
],
_class: "com.vilya.farm.domain.model.User"
},
{
_id: ObjectId("6414667360e4ba4481052627"),
email: "[email protected]",
password: "$2a$10$OP52phZ61l2JX2e2TQOu9ubYFBYcPeqEZ92ox2Nyyp5e.MEZk7GhS",
firstName: "f",
lastName: "l",
_class: "com.vilya.farm.domain.model.User"
}
]
}
Note the plot
field in user_have_plots
collection and plots
field in the user
collection. They are an object. To make it work, try this:
db.users.aggregate([
{
"$lookup": {
"from": "users_have_plots",
"let": {
"plots": "$plots"
},
"pipeline": [
{
"$match": {
"$expr": {
"$in": [
"$_id",
{
"$ifNull": [
"$$plots.$id",
[]
]
}
]
}
}
},
{
"$lookup": {
"from": "plots",
"localField": "plot.$id",
"foreignField": "_id",
"as": "plot"
}
}
],
"as": "plots"
}
}
])
Finally, you don't need to use Dbrefs
, they are suitable for purposes when you have joined a single property with multiple collections, or with collections in multiple databases. All your collections are in the same databases, use simple references.