Retrieve the related objects in MongoDB without a localField
up vote
0
down vote
favorite
I use MongoDB to store Call Detail Records, which consist of A-legs and B-legs.
They are related as follows:
- All records (A & B-legs) are in the same
cdrcollection - A-legs have a field
leg_typethat equals toa, B-legs havebof course - B-legs have a field
a_legto indicate to what A-leg they belong.
At the moment we retrieve the A-legs we want, then loop through them and for every A-leg we retrieve the related B-legs (can be multiple), so all on the clientside.
I was wondering if I could do that in one query, and apparently you can with $lookup (aggregation). However it seems to be required that you can reference a field on the A-leg in this case, which would be an array of B-legs.
But I don't have that field, and before I spend unnecessary time to have such a field I was wondering if I could do it differently.
For completeness, this is how we retrieve the CDR's now:
a_legs = mongo_db['cdr']
.find({'group_id': group.id, 'leg_type': 'a'})
.sort('times.created', pymongo.DESCENDING)
.limit(50)
for cdr in a_legs:
# Find B-legs
cdr['b_legs'] = mongo_db['cdr']
.find({'a_leg': cdr['call_id'], 'leg_type': 'b'})
.sort('times.created', pymongo.ASCENDING)
So the bottomline question: can we do the above in a single query to MongoDB?
I tried doing it like this:
db.cdr.aggregate([{
$lookup: {
from: "cdr",
localField: "call_id",
foreignField: "a_leg",
as: "b_legs"
}
}])
But it shows me no results.
mongodb aggregation
asked Nov 9 at 8:56
Maarten Ureel
7619
add a comment |
up vote
0
down vote
favorite
I use MongoDB to store Call Detail Records, which consist of A-legs and B-legs.
They are related as follows:
- All records (A & B-legs) are in the same
cdrcollection - A-legs have a field
leg_typethat equals toa, B-legs havebof course - B-legs have a field
a_legto indicate to what A-leg they belong.
At the moment we retrieve the A-legs we want, then loop through them and for every A-leg we retrieve the related B-legs (can be multiple), so all on the clientside.
I was wondering if I could do that in one query, and apparently you can with $lookup (aggregation). However it seems to be required that you can reference a field on the A-leg in this case, which would be an array of B-legs.
But I don't have that field, and before I spend unnecessary time to have such a field I was wondering if I could do it differently.
For completeness, this is how we retrieve the CDR's now:
a_legs = mongo_db['cdr']
.find({'group_id': group.id, 'leg_type': 'a'})
.sort('times.created', pymongo.DESCENDING)
.limit(50)
for cdr in a_legs:
# Find B-legs
cdr['b_legs'] = mongo_db['cdr']
.find({'a_leg': cdr['call_id'], 'leg_type': 'b'})
.sort('times.created', pymongo.ASCENDING)
So the bottomline question: can we do the above in a single query to MongoDB?
I tried doing it like this:
db.cdr.aggregate([{
$lookup: {
from: "cdr",
localField: "call_id",
foreignField: "a_leg",
as: "b_legs"
}
}])
But it shows me no results.
mongodb aggregation
asked Nov 9 at 8:56
Maarten Ureel
7619
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I use MongoDB to store Call Detail Records, which consist of A-legs and B-legs.
They are related as follows:
- All records (A & B-legs) are in the same
cdrcollection - A-legs have a field
leg_typethat equals toa, B-legs havebof course - B-legs have a field
a_legto indicate to what A-leg they belong.
At the moment we retrieve the A-legs we want, then loop through them and for every A-leg we retrieve the related B-legs (can be multiple), so all on the clientside.
I was wondering if I could do that in one query, and apparently you can with $lookup (aggregation). However it seems to be required that you can reference a field on the A-leg in this case, which would be an array of B-legs.
But I don't have that field, and before I spend unnecessary time to have such a field I was wondering if I could do it differently.
For completeness, this is how we retrieve the CDR's now:
a_legs = mongo_db['cdr']
.find({'group_id': group.id, 'leg_type': 'a'})
.sort('times.created', pymongo.DESCENDING)
.limit(50)
for cdr in a_legs:
# Find B-legs
cdr['b_legs'] = mongo_db['cdr']
.find({'a_leg': cdr['call_id'], 'leg_type': 'b'})
.sort('times.created', pymongo.ASCENDING)
So the bottomline question: can we do the above in a single query to MongoDB?
I tried doing it like this:
db.cdr.aggregate([{
$lookup: {
from: "cdr",
localField: "call_id",
foreignField: "a_leg",
as: "b_legs"
}
}])
But it shows me no results.
mongodb aggregation
asked Nov 9 at 8:56
Maarten Ureel
7619
I use MongoDB to store Call Detail Records, which consist of A-legs and B-legs.
They are related as follows:
- All records (A & B-legs) are in the same
cdrcollection - A-legs have a field
leg_typethat equals toa, B-legs havebof course - B-legs have a field
a_legto indicate to what A-leg they belong.
At the moment we retrieve the A-legs we want, then loop through them and for every A-leg we retrieve the related B-legs (can be multiple), so all on the clientside.
I was wondering if I could do that in one query, and apparently you can with $lookup (aggregation). However it seems to be required that you can reference a field on the A-leg in this case, which would be an array of B-legs.
But I don't have that field, and before I spend unnecessary time to have such a field I was wondering if I could do it differently.
For completeness, this is how we retrieve the CDR's now:
a_legs = mongo_db['cdr']
.find({'group_id': group.id, 'leg_type': 'a'})
.sort('times.created', pymongo.DESCENDING)
.limit(50)
for cdr in a_legs:
# Find B-legs
cdr['b_legs'] = mongo_db['cdr']
.find({'a_leg': cdr['call_id'], 'leg_type': 'b'})
.sort('times.created', pymongo.ASCENDING)
So the bottomline question: can we do the above in a single query to MongoDB?
I tried doing it like this:
db.cdr.aggregate([{
$lookup: {
from: "cdr",
localField: "call_id",
foreignField: "a_leg",
as: "b_legs"
}
}])
But it shows me no results.
mongodb aggregation
mongodb aggregation
asked Nov 9 at 8:56
Maarten Ureel
7619
asked Nov 9 at 8:56
Maarten Ureel
7619
edited Nov 9 at 9:32
asked Nov 9 at 8:56
Maarten Ureel
7619
asked Nov 9 at 8:56
Maarten Ureel
7619
asked Nov 9 at 8:56
Maarten Ureel
7619
7619
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
I figured it out with the help of Studio3T which helped me about to construct the query step by step. This is what I ended up with:
mongo_db['cdr'].aggregate(
[
{
"$match" : {
"group_id" : 585,
"leg_type" : "a"
}
},
{
"$lookup" : {
"from" : "cdr",
"let" : {
"call_id" : "$call_id"
},
"pipeline" : [
{
"$match" : {
"$expr" : {
"$and" : [
{
"$eq" : [
"$leg_type",
"b"
]
},
{
"$eq" : [
"$a_leg",
"$$call_id"
]
}
]
}
}
},
{
"$project" : {
"_id" : False,
"raw" : False,
"leg_type" : False
}
},
{
"$sort" : {
"times.created" : 1
}
}
],
"as" : "b_legs"
}
},
{
"$project" : {
"_id" : False,
"raw" : False,
"leg_type" : False
}
}
],
{
"allowDiskUse" : False
}
);
I needed to use $lookup together with pipeline. I also had to create an index ofcourse on call_id to make it work fast.
answered Nov 9 at 10:56
Maarten Ureel
7619
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
I figured it out with the help of Studio3T which helped me about to construct the query step by step. This is what I ended up with:
mongo_db['cdr'].aggregate(
[
{
"$match" : {
"group_id" : 585,
"leg_type" : "a"
}
},
{
"$lookup" : {
"from" : "cdr",
"let" : {
"call_id" : "$call_id"
},
"pipeline" : [
{
"$match" : {
"$expr" : {
"$and" : [
{
"$eq" : [
"$leg_type",
"b"
]
},
{
"$eq" : [
"$a_leg",
"$$call_id"
]
}
]
}
}
},
{
"$project" : {
"_id" : False,
"raw" : False,
"leg_type" : False
}
},
{
"$sort" : {
"times.created" : 1
}
}
],
"as" : "b_legs"
}
},
{
"$project" : {
"_id" : False,
"raw" : False,
"leg_type" : False
}
}
],
{
"allowDiskUse" : False
}
);
I needed to use $lookup together with pipeline. I also had to create an index ofcourse on call_id to make it work fast.
answered Nov 9 at 10:56
Maarten Ureel
7619
add a comment |
up vote
0
down vote
I figured it out with the help of Studio3T which helped me about to construct the query step by step. This is what I ended up with:
mongo_db['cdr'].aggregate(
[
{
"$match" : {
"group_id" : 585,
"leg_type" : "a"
}
},
{
"$lookup" : {
"from" : "cdr",
"let" : {
"call_id" : "$call_id"
},
"pipeline" : [
{
"$match" : {
"$expr" : {
"$and" : [
{
"$eq" : [
"$leg_type",
"b"
]
},
{
"$eq" : [
"$a_leg",
"$$call_id"
]
}
]
}
}
},
{
"$project" : {
"_id" : False,
"raw" : False,
"leg_type" : False
}
},
{
"$sort" : {
"times.created" : 1
}
}
],
"as" : "b_legs"
}
},
{
"$project" : {
"_id" : False,
"raw" : False,
"leg_type" : False
}
}
],
{
"allowDiskUse" : False
}
);
I needed to use $lookup together with pipeline. I also had to create an index ofcourse on call_id to make it work fast.
answered Nov 9 at 10:56
Maarten Ureel
7619
add a comment |
up vote
0
down vote
up vote
0
down vote
I figured it out with the help of Studio3T which helped me about to construct the query step by step. This is what I ended up with:
mongo_db['cdr'].aggregate(
[
{
"$match" : {
"group_id" : 585,
"leg_type" : "a"
}
},
{
"$lookup" : {
"from" : "cdr",
"let" : {
"call_id" : "$call_id"
},
"pipeline" : [
{
"$match" : {
"$expr" : {
"$and" : [
{
"$eq" : [
"$leg_type",
"b"
]
},
{
"$eq" : [
"$a_leg",
"$$call_id"
]
}
]
}
}
},
{
"$project" : {
"_id" : False,
"raw" : False,
"leg_type" : False
}
},
{
"$sort" : {
"times.created" : 1
}
}
],
"as" : "b_legs"
}
},
{
"$project" : {
"_id" : False,
"raw" : False,
"leg_type" : False
}
}
],
{
"allowDiskUse" : False
}
);
I needed to use $lookup together with pipeline. I also had to create an index ofcourse on call_id to make it work fast.
answered Nov 9 at 10:56
Maarten Ureel
7619
I figured it out with the help of Studio3T which helped me about to construct the query step by step. This is what I ended up with:
mongo_db['cdr'].aggregate(
[
{
"$match" : {
"group_id" : 585,
"leg_type" : "a"
}
},
{
"$lookup" : {
"from" : "cdr",
"let" : {
"call_id" : "$call_id"
},
"pipeline" : [
{
"$match" : {
"$expr" : {
"$and" : [
{
"$eq" : [
"$leg_type",
"b"
]
},
{
"$eq" : [
"$a_leg",
"$$call_id"
]
}
]
}
}
},
{
"$project" : {
"_id" : False,
"raw" : False,
"leg_type" : False
}
},
{
"$sort" : {
"times.created" : 1
}
}
],
"as" : "b_legs"
}
},
{
"$project" : {
"_id" : False,
"raw" : False,
"leg_type" : False
}
}
],
{
"allowDiskUse" : False
}
);
I needed to use $lookup together with pipeline. I also had to create an index ofcourse on call_id to make it work fast.
answered Nov 9 at 10:56
Maarten Ureel
7619
answered Nov 9 at 10:56
Maarten Ureel
7619
answered Nov 9 at 10:56
Maarten Ureel
7619
answered Nov 9 at 10:56
Maarten Ureel
7619
7619
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53222540%2fretrieve-the-related-objects-in-mongodb-without-a-localfield%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
