"Updated" I would like the details(found in the card schema) to be inserted into the catagory.cards array. So that I can map the results and use the code below for example.
items[i].catagory[a].cards[c].image
2 schemas users and cards
I have user.catagories stored under user.catagory and cards associated with the catagory.cards listed in an array. I have all the card data stored in a cards schema. (see below)
user & cards
db={
"User": [
{
"_id": {
"$oid": "650ee3f7f19dcf3789acdee1"
},
"verified": true,
"catagory": [
{
"name": "trade",
"createdAt": {
"$date": "2024-02-01T21:47:34.215Z"
},
"_id": {
"$oid": "65bc1176cfb7da6997273ae3"
},
"cards": [
{
"$oid": "65c43a9539206da4c953e334"
},
{
"$oid": "65c43a9539206da4c953e32e"
},
{
"$oid": "65c43a9539206da4c953e332"
},
{
"$oid": "65d2447cbad1f058de61ecca"
},
{
"$oid": "65d2447cbad1f058de61ecca"
}
]
},
{
"name": "baseball",
"createdAt": {
"$date": "2024-02-01T23:12:46.996Z"
},
"_id": {
"$oid": "65bc256ecfb7da6997273c71"
}
},
{
"name": "hockey",
"createdAt": {
"$date": "2024-02-14T00:56:49.771Z"
},
"_id": {
"$oid": "65cc0fd1fa50656dceec84af"
}
},
{
"name": "mma",
"createdAt": {
"$date": "2024-02-14T21:10:46.101Z"
},
"_id": {
"$oid": "65cd2c56db3d4b491e98a1e5"
}
},
{
"name": "football",
"createdAt": {
"$date": "2024-02-17T01:47:47.492Z"
},
"_id": {
"$oid": "65d010432c4c6cd6b8b83682"
},
"cards": [
{
"$oid": "65d2447cbad1f058de61ecd1"
}
]
},
{
"name": "ufc",
"createdAt": {
"$date": "2024-02-17T01:48:11.291Z"
},
"_id": {
"$oid": "65d0105b2c4c6cd6b8b836a6"
}
},
{
"name": "test",
"createdAt": {
"$date": "2024-02-17T03:21:42.524Z"
},
"_id": {
"$oid": "65d026462c4c6cd6b8b836dc"
}
}
]
}
],
"cards": [
{
"_id": "65c43a9539206da4c953e334",
"title": "PSA 9 1996 Topps Finest Gold w/ Coating #269 Kobe Bryant Rookie Card",
"date": "2023-11-16T05:00:00.000Z",
"price": 125000,
"image": "s-l300.webp",
"search": [],
"createdAt": "2024-02-08T02:21:09.344Z",
"updatedAt": "2024-02-08T02:21:09.344Z",
"__v": 0
},
{
"_id": "65c43a9539206da4c953e32e",
"title": "1996-97 Topps Finest Bronze Refractor #74 Kobe Bryant (RC) PSA 7 W/ Coating",
"date": "2024-02-01T05:00:00.000Z",
"price": 130000,
"image": "s-l300.webp",
"search": [],
"createdAt": "2024-02-08T02:21:09.344Z",
"updatedAt": "2024-02-08T02:21:09.344Z",
"__v": 0
},
{
"_id": "65c43a9539206da4c953e332",
"title": "1996-97 Fleer Ultra Series 2 Basketball Factory Sealed Hobby Box-Kobe Bryant RC?",
"date": "2023-11-28T05:00:00.000Z",
"price": 124999,
"image": "s-l300.webp",
"search": [],
"createdAt": "2024-02-08T02:21:09.344Z",
"updatedAt": "2024-02-08T02:21:09.344Z",
"__v": 0
},
{
"_id": "65d2447cbad1f058de61ecca",
"title": "HUGE SPORTS CARD COLLECTION! 2023 CJ Stroud Justin Herbert Rookie Auto RPA READ",
"date": "2024-02-17T05:00:00.000Z",
"price": 75000,
"image": "s-l300.webp",
"search": [],
"createdAt": "2024-02-18T17:55:08.414Z",
"updatedAt": "2024-02-18T17:55:08.414Z",
"__v": 0
}
]
}
Aggregate: This code gets me very close to what I desire. However, it attaches the result card array and overwrites the catagory.cards array instead of only updating based on 'users.catagory.cards._id' === 'cards._id'.
const results = await User.aggregate(
[
{
$lookup: {
from: 'cards',
localField: 'catagory.cards',
foreignField: '_id',
as: 'cards'
}
},
{
$addFields: { 'catagory.cards': '$cards' }
}
],
{ maxTimeMS: 60000, allowDiskUse: true }
);
I tried using a filter but this was not successful.
{
"catagory.cards": {
$filter: {
input: "$catagory.cards",
as: "card",
cond: { $in: [ "$$card._id", "$catagory.cards"] }
}
}
}
first of all would be great to see the question - what exactly you would like to achieve. For now, let me try with some guess, and let me know if it helps. Otherwise please provide with more specific expectations, so that I can update the answer.
First thing I noted is localField in your example in $lookup has 'catagory.cards', while it should be 'catagory.cards._id' as you match only by _id field, not by the whole record.
Also, noticed a typo "catagory" instead of "category". If it wasn't intentional - please be careful, because it might be root of some issues if you'll write category in other part of code.
Now let me guess you would like to see all card details inside array items of category.cards field.
That can be achieved by:
$unwind-ing arrays into separate records (we have to unwind categories into separate records to have each category in each record, then unwind category cards to have each card in separate record),$lookup into category.cards field (so that it will override that field for each record - and that's why it was important to have each record per each category card),Please note in $unwind I used flag preserveNullAndEmptyArrays: true, otherwise it would throw away items without unwinding children (in your example: category "baseball").
db.User.aggregate([
{ $unwind: {
path: '$catagory',
preserveNullAndEmptyArrays: true
}},
{ $unwind: {
path: '$catagory.cards',
preserveNullAndEmptyArrays: true
}},
{ $lookup: {
from: 'cards',
localField: 'catagory.cards._id',
foreignField: '_id',
as: 'catagory.cards'
}},
{ $group: {
_id: '$catagory._id',
userId: { $first: '$_id' },
categoryId: { $first: '$catagory._id' },
name: { $first: '$catagory.name' },
createdAt: { $first: '$catagory.createdAt' },
cards: { $push: { $arrayElemAt: ['$catagory.cards', 0] } }
}},
{ $group: {
_id: '$userId',
catagory: { $addToSet: {
_id: '$categoryId',
name: '$name',
createdAt: '$createdAt',
cards: '$cards'
}},
}},
]);
I tried it and it provides me the following output:
{
"_id" : ObjectId("65d3d362c2006d4572b028a3"),
"catagory" : [
{
"_id" : "65bc1176cfb7da6997273ae3",
"name" : "test",
"createdAt" : "2024-02-01T21:47:34.215Z",
"cards" : [
{
"_id" : "65c43a9539206da4c953e334",
"title" : "PSA 9 1996 Topps Finest Gold w/ Coating #269 Kobe Bryant Rookie Card",
"date" : "2023-11-16T05:00:00.000Z",
"price" : NumberInt(125000),
"image" : "s-l300.webp",
"search" : [
],
"createdAt" : "2024-02-08T02:21:09.344Z",
"updatedAt" : "2024-02-08T02:21:09.344Z",
"__v" : NumberInt(0)
},
{
"_id" : "65c43a9539206da4c953e32e",
"title" : "1996-97 Topps Finest Bronze Refractor #74 Kobe Bryant (RC) PSA 7 W/ Coating",
"date" : "2024-02-01T05:00:00.000Z",
"price" : NumberInt(130000),
"image" : "s-l300.webp",
"search" : [
],
"createdAt" : "2024-02-08T02:21:09.344Z",
"updatedAt" : "2024-02-08T02:21:09.344Z",
"__v" : NumberInt(0)
},
{
"_id" : "65c43a9539206da4c953e332",
"title" : "1996-97 Fleer Ultra Series 2 Basketball Factory Sealed Hobby Box-Kobe Bryant RC?",
"date" : "2023-11-28T05:00:00.000Z",
"price" : NumberInt(124999),
"image" : "s-l300.webp",
"search" : [
],
"createdAt" : "2024-02-08T02:21:09.344Z",
"updatedAt" : "2024-02-08T02:21:09.344Z",
"__v" : NumberInt(0)
},
{
"_id" : "65d2447cbad1f058de61ecca",
"title" : "HUGE SPORTS CARD COLLECTION! 2023 CJ Stroud Justin Herbert Rookie Auto RPA READ",
"date" : "2024-02-17T05:00:00.000Z",
"price" : NumberInt(75000),
"image" : "s-l300.webp",
"search" : [
],
"createdAt" : "2024-02-18T17:55:08.414Z",
"updatedAt" : "2024-02-18T17:55:08.414Z",
"__v" : NumberInt(0)
}
]
},
{
"_id" : "65bc256ecfb7da6997273c71",
"name" : "baseball",
"createdAt" : "2024-02-01T23:12:46.996Z",
"cards" : [
]
}
]
}
Please let me know if that's result you want to achieve, and if you have any questions or prefer any further clarifications.
Welcome!
UPDATE
Here is the MongoPlayground: https://mongoplayground.net/p/4Fd8KLywV4v