I have 2 collections in mongoDB namely product
and author
. Now the product
collection has an object called the detail
which has the title
and other details whereas the author
table has details regarding the author
. Now i am trying to make a search that finds both title
and author
.. For eg: if the title
is matched the details
of that product
should be listed and if the author
is matched then again the details
of that product
should be shown. The author_id
is a foreign_key
in the product
collection and the _id
is a primary key in the author collection.
Below is my collection
The Product
Collection
{
"_id" : ObjectId("5af2bd44a003533a8abf4e56"),
"product_type_id" : "5ae834807ae0d9538e45ab45",
"date_added" : "2018-03-12 12-3-54",
"status" : 1,
"detail" : {
"title" : "The random title",
"author_id" : "5af2c401a003533a8abf4e57",
"test_id" : 12345,
"description" : "I have a mongoDB collection with f rows",
"mrp" : 200,
"binding" : "sd",
"language" : "English",
"isbn" : NumberLong("9788700631625"),
"isbn_10" : 747532745,
"ean" : 897655,
"pages" : 200
}
}
This is the author
collection
{
"_id" : ObjectId("5af2c401a003533a8abf4e57"),
"name" : "Dan Brown",
"test_id" : 12345
}
So far i have to search on the collections independently . But not together
For author
search
db.author.aggregate
([{
"$match":
{
"name":"Dan Brown"
}
},
{
"$lookup":
{
"from": "product",
"localField": "test_id",
"foreignField": "detail.test_id",
"as": "users"
}
}
]
).pretty();
And title
has been accessed using the normal find()
method
EDIT 1:
So this is what i have tried so far. Is this the right approach . Btw it's still not working though
db.product.aggregate
(
[
{
"$match":
{
detail.title : 'The random title'
}
},
{
"$lookup":
{
"from": "author",
"localField": "test_id",
"foreignField": "detail.test_id",
"as": "tb2"
}
},
{
"$unwind": "$tb2"
},
{
"$match":
{
"tb2.name": "agent"
}
}
]
);