I have three collections: “widgets”, “users”, and “accounts”. The documents within each look like:
accounts:
[
{
"_id": 1,
"name": "account_1",
"account": "abc123"
},
{
"_id": 2,
"name": "account_2",
"account": "def456"
}
]
users:
[
{
"_id": 1,
"username": "jsmith",
"accounts": ["abc123", "def456"]
}
]
widgets:
[
{
"_id": 1,
"name": "widget_1",
"user_id": 1,
"account_id": 1,
"expires": "2022-11-30T00:00:00.000-05:00"
},
{
"_id": 2,
"name": "widget_2",
"user_id": 1,
"account_id": 2,
"expires": "2022-12-31T14:00:00.000-05:00"
}
]
Notice that a single “user” can be a member of multiple “accounts”, and that a single user can have multiple “widgets”. My goal is to write a query such that results look like:
[
{
"_id": 1,
"username": "jsmith",
"accounts": [
{
"_id": 1,
"name": "account_1",
"account": "abc123"
}
],
"widgets": [
{
"_id": 1,
"name": "widget_1",
"expires": "2022-11-30T00:00:00.000-05:00"
}
]
}
]
In other words the query:
- Finds all users that have widgets expiring on a certain date (2022-11-30 in the example)
- Matches widgets and users by
_id
, but accounts by theaccount
attribute - Rolls the widgets documents into the returned user document
- Rolls the accounts associated with the found widgets into the returned user document
I have successfully written an aggregate query that finds all widgets expiring on a certain date and rolls the associated user and account documents into the token document. But it’s not clear to me if it will be possible to do as I have described above. I think this involves the pipeline
option on the $lookup
function, but I am not sure.
For reference, the query I wrote (that I am trying to flip) which returns tokens with rolled-up documents is:
db.getCollection("widgets").aggregate([
{$match: {
expires: {
$lt: ISODate("2022-12-01T20:00:00.000-05:00"),
$gt: ISODate("2022-11-29T18:59:59.999-05:00")
}
}},
{$lookup: {
from: "users",
localField: "user_id",
foreignField: "_id",
as: "user"
}},
{$lookup: {
from: "accounts",
localField: "account_id",
foreignField: "_id",
as: "account"
}},
{$unwind: "$user"},
{$unwind: "$account"}
])
Note: my target database version is 4.4.