Hi
I have below structure data as child and parent
{
"_id": {
"$oid": "64a67f32dbe7c36e2e6c15c8"
},
"name": {
"user": "xyz"
},
"data": {
"age": "55",
"address": [{
"$ref": "Addresses",
"$id": {
"$oid": "64a67f2fdbe7c36e2e6c15c6"
}
}, {
"$ref": "Addresses",
"$id": {
"$oid": "64a67f2fdbe7c36e2e6c15c7"
}
},
]
}
}
Child records
{
"_id": {
"$oid": "64a67f2fdbe7c36e2e6c15c6"
},
"name": {
"type": "permenant"
},
"address":"permenant address 1"
}
==========================
{
"_id": {
"$oid": "64a67f2fdbe7c36e2e6c15c7"
},
"name": {
"type": "secondary"
},
"address":"permenant address 2"
}
I’m trying to build query but not able to find suitable output as per my requirement as below
{
"_id": {
"$oid": "64a67f32dbe7c36e2e6c15c8"
},
"name": {
"user": "xyz"
},
"data": {
"age": "55",
"address": [
{
"_id": {
"$oid": "64a67f2fdbe7c36e2e6c15c6"
},
"name": {
"type": "permenant"
},
"address": "permenant address 1"
},
{
"_id": {
"$oid": "64a67f2fdbe7c36e2e6c15c7"
},
"name": {
"type": "secondary"
},
"address": "permenant address 2"
}
]
}
}
I think you’ll need to unwind the address to be able to lookup each element of the array and then put it back together again, something like this:
db.getCollection("Parent").aggregate([
{
$unwind:'$data.address'
},
{
$lookup:{
from:'Child',
localField:'data.address.id',
foreignField:'_id',
as:'lookupAddress'
}
},
{
$group:{
_id:{
_id:'$_id',
name:'$name',
age:'$data.age'
},
addressInfo:{$push:'$lookupAddress'}
}
},
{
$project:{
_id:'$_id._id',
data:{
age:'$_id.age',
address:'$addressInfo'
}
}
}
])
address:‘$addressInfo’ → returns array of array and also not returning respective foreign records. Its fetching all address which are not belongs to parent key.
I don’t see that:
This just does the unwind and lookup, you can see that each row just has the matching lookup and the unmatched data is not included in the output.
Can you create a mongo playground with the data you’re using as well the query to show it not working?
I have created mongo playground at below url:
Child should be lowercase “c”:
I have updated few things in below url
Existing Query ouptput:
"address": [
[
{
"_id": ObjectId("64a67f2fdbe7c36e2e6c15c6"),
"address": "permenant address 1",
"name": {
"type": "permenant"
}
}
],
[
{
"_id": ObjectId("64a67f2fdbe7c36e2e6c15c7"),
"address": "permenant address 2",
"name": {
"type": "secondary"
}
}
]
],
Expected output:
"address": [
{
"_id": ObjectId("64a67f2fdbe7c36e2e6c15c6"),
"address": "permenant address 1",
"name": {
"type": "permenant"
}
},
{
"_id": ObjectId("64a67f2fdbe7c36e2e6c15c7"),
"address": "permenant address 2",
"name": {
"type": "secondary"
}
}
],
Ok, this was as the lookup stage returns an array so we have arrays of arrays when we recombine them, you can strip it down to one item with an arrayElemAt operator:
db.parent.aggregate([
{
$unwind: "$data.address"
},
{
$lookup: {
from: "Address",
localField: "data.address.id",
foreignField: "_id",
as: "lookupAddress"
}
},
{
$addFields: {
"lookupAddress": {
"$arrayElemAt": [
"$lookupAddress",
0
]
}
}
},
{
$group: {
_id: {
_id: "$_id",
name: "$name",
age: "$data.age"
},
addressInfo: {
$push: "$lookupAddress"
}
}
},
{
$project: {
_id: "$_id._id",
data: {
age: "$_id.age",
address: "$addressInfo"
}
}
}
])
It gives me perfect result in Mongo Playground but strange result I’m getting in MongoDB.
"address": [
{
"_id": ObjectId("64a67f2fdbe7c36e2e6c15c6"),
"address": "permenant address 1",
"name": {
"type": "permenant"
}
},
{
"_id": ObjectId("64a67f2fdbe7c36e2e6c15c6"),
"address": "permenant address 1",
"name": {
"type": "permenant"
}
}
],
That’s strange, if you remove the stages after the $lookup, what’s the output you get?
I might need to reframe question. I have use dbref in my actual database and that is causing strange result. I have validated both way and If I’m using below DBRef then it gives me first Address object as Element we are selecting 0.
"address" : [
DBRef("Address", ObjectId("64b5325a8795d410fdf06151")),
DBRef("Address", ObjectId("64b5328d8795d410fdf06152"))
]
Aha, sorry I missed that element of the issue, I’ve not used DBRef before, this seems to show a workaround using them in a pipeline:
So adding that in:
db.getCollection("Parent2").aggregate([
{
$unwind:'$data.address'
},
{
$addFields:{
'refid':{$arrayElemAt:[{$objectToArray:'$data.address'}, 1]}
}
},
{
$lookup:{
from:'child',
localField:'refid.v',
foreignField:'_id',
as:'lookupAddress'
}
},
{
$unwind:'$lookupAddress'
},
{
$group:{
_id:{
_id:'$_id',
name:'$name',
age:'$data.age'
},
addressInfo:{$push:'$lookupAddress'}
}
},
{
$project:{
_id:'$_id._id',
data:{
age:'$_id.age',
address:'$addressInfo'
}
}
},
])
Thanks, last solution works.
Excellent, sorry I missed the DBRef at the start…not like you didn’t put it in the title!
1 Like
Thanks, I’m newbie for mongo and not sure what it should call.
system
July 22, 2023, 2:24pm
17
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.
