If you have an array of id and you want to retrieve data from another table and filter the properties to return you will use the aggregate feature of mongo and create a pipeline like so:
$match - to retrieve the document you're interested in and the list of id you want to proceed
$lookup - to join the other table
$group or $project - format your data
Let's make this simple with a real life example.
Imagine you have a Library, this library contains a list of unavailable Books.
In your Library collection you store an array of Id representing all the unavailable books for this library.
Our objective: retrieve all unavaible books (name and id) for a specific library.
Library {
id: string,
name: string,
unavailable_books: string[]
}
Book {
id: string,
title: string,
author: string
}
{
$match: {
id: libraryId
}
}
{
$lookup: {
from: 'books',
localField: 'unavailable_books',
foreignField: 'id',
as: 'unavailableBooks',
},
}
With this query you retrieve all the properties of both collections.
Now that we have all the data. We can use multiple approaches.
$group
You can create a $group step an use $push to create and array containing only the properties you want:
{
$match: {
id: libraryId
}
},
{
$lookup: {
from: "books",
localField: "unavailable_books",
foreignField: "id",
as: "unavailableBooks"
}
},
{
$group: {
_id: "$id",
unavailable: {
$push: {
id: "$unavailableBooks.id",
title: "$unavailableBooks.title"
}
}
}
}
[
{
"_id": 1,
"unavailable": [
{
"id": [
1,
3,
4
],
"title": [
"Hamlet",
"1984",
"Don Quichotte"
]
}
]
}
]
But it return the book id and name in two separate array which is not what we want.
💡
If you don't have any data in your
$unavailableBooks it return the same result but "unavailable" contains an object with empty array:
Mongo Playground Link
$project
Using a $project makes things easy because you can use $cond
💡
$cond cannot be used in a $group step
$cond work exactly like the if/else condition:
$cond: {
"if": { condition },
"then":
"else":
}
Now we can say: If unavailableBooks is empty, return [] otherwise push my data in "unavailable"
By using the $map operator instead of the $push :
💡
$push cannot be used in a $project step
{
$match: {
id: 1
}
},
{
$lookup: {
from: "books",
localField: "unavailable_books",
foreignField: "id",
as: "unavailableBooks"
}
},
{
$project: {
_id: "$id",
name: "$name",
unavailable: {
$cond: {
"if": {
$eq: [
"unavailableBooks",
[]
]
},
"then": [],
"else": {
$map: {
input: "$unavailableBooks",
as: "d",
in: {
firstName: "$$d.id",
lastName: "$$d.title"
}
}
}
}
}
}
}
[
{
"_id": 1,
"name": "Library 1",
"unavailable": [
{
"firstName": 1,
"lastName": "Hamlet"
},
{
"firstName": 3,
"lastName": "1984"
},
{
"firstName": 4,
"lastName": "Don Quichotte"
}
]
}
]
Conclusion
This is what I've found for now. It wasn't easy to get my head around this.
I feel like using the $project step to do so is strange as this step is supposed to filter wich field must be return but I didn't found a solution that work with the $group step yet.
If you know any other method to get the same result from a $group let me know!