mongodb - aggregating and unwinding foreign ref documents
Clash Royale CLAN TAG#URR8PPP
mongodb - aggregating and unwinding foreign ref documents
So for my example database set up:
db.lists.insertMany([
_id: "1", name: "list1", included_lists: ["2"], items: ["i1"] ,
_id: "2", name: "list2", included_lists: , items: ["i2", "i3"]
])
db.items.insertMany([
_id: "i1", name: "item1", details: [, , ] ,
_id: "i2", name: "item2", details: [, , ] ,
_id: "i3", name: "item3", details: [, , ]
])
I'm currently getting my items data via:
db.lists.aggregate([
"$match": "_id": "$in": ["1", "2"] ,
"$lookup":
"from": "items",
"localField": "items",
"foreignField": "_id",
"as": "item"
,
"$unwind": "$item" ,
"$facet":
"results": [
"$skip": 0 ,
"$limit": 10 ,
"$project":
name: 1,
item: 1
],
"total": [
"$count": "total" ,
]
]).pretty()
which returns:
"results" : [
"_id" : "1",
"name" : "list1",
"item" :
"_id" : "i1",
"name" : "item1",
"details" : [
,
,
]
,
"_id" : "2",
"name" : "list2",
"item" :
"_id" : "i2",
"name" : "item2",
"details" : [
,
,
]
,
"_id" : "2",
"name" : "list2",
"item" :
"_id" : "i3",
"name" : "item3",
"details" : [
,
,
]
],
"total" : [
"total" : 3
]
What I'm trying to do, is remove the "$match": "_id": "$in": ["1", "2"] , as I want to remove the query needed to get the array of ids, and instead just get all the ids from list _id and its included_lists ids. Then have return all the items return like my result.
"$match": "_id": "$in": ["1", "2"] ,
_id
included_lists
items
This question is similar to: mongodb - unwinding nested subdocuments but I've reasked due to ambiguity and lack of db documents.
@AmitWagner I don't want to run all over the list documents. I only want what's in desired list
_id and anything that's also in included_lists. So if I were to target list "2", I would only return 2 results max.– A. Lau
Aug 13 at 7:21
_id
included_lists
"2"
can you post the expected output as well
– Anthony Winzlet
Aug 13 at 7:40
1 Answer
1
you can do it with graph lookup and then group
db.lists.aggregate([
"$match": "_id": "$in": ["1"] ,
$graphLookup:
from: "lists",
startWith: "$_id" ,
connectFromField: "included_lists",
connectToField: "_id",
as: "connected",
,
$unwind:"$connected",
$group:_id:"$connected._id",items:$first:'$connected.items',name:$first:'$connected.name',
"$lookup":
"from": "items",
"localField": "items",
"foreignField": "_id",
"as": "item"
,
"$unwind": "$item" ,
"$facet":
"results": [
"$skip": 0 ,
"$limit": 10 ,
"$project":
name: 1,
item: 1
],
"total": [
"$count": "total" ,
]
]).pretty()
If I wanted to add a
$match statement for the unwound items, where should I put it?– A. Lau
Aug 14 at 0:44
$match
items
Ah, added it after the
$unwind– A. Lau
Aug 14 at 1:02
$unwind
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
why do you need the included list if you want to run over all the list documents anyway ? it sounds like you just don`t wont the match statement
– Amit Wagner
Aug 13 at 7:18