Arrays and Projections, $filter, $unwrap, $project

Working with Data
1

Hi All,

I’m slowly getting used to some of the concepts I’ve mentioned here and I believe I have a good schema, which looks like the following at high level.

[ 
{"AccountId": "Account1", 
"Sites" : [ { "SiteId": "Site1-1",  "UserRoles" : [ { "UserId" : "1", "RoleId" : "1" } ] }, {"SiteId" : "Site1-2" } ] 
}
]

What I’m actually struggling with here, is not so much the filtering but projecting, there is an instance where I need to get the list of Sites and I don’t need the Accounts, i.e Getting a list of Sites that users have access to but I don’t really need the Account information in that case. Or would I actually be better off just returning the list of accounts and then processing the Sites that have been filtered using unwrap and $project to get my list of Sites, this is my current query.

[
    { 
        "$match" : { 
            "UserRoles" : { 
                "$elemMatch" : { 
                    "DorsaviUserId" : "D274575B-472D-41BA-9F88-5A65965AE491"
                }
            }
        }
    }, 
    { 
        "$unwind" : "$Sites"
    }, 
    { 
        "$unset" : [
            "Name", 
            "Description", 
            "Contacts", 
            "UserRoles", 
            "CreatedUtc", 
            "CreatedUtcLocale", 
            "CreatedBy", 
            "ModifiedUtc", 
            "ModifiedUtcLocale", 
            "ModifiedBy", 
            "Status", 
            "Reference", 
            "AccountId", 
            "ParentAccountId", 
            "Invitations", 
            "Sites.UserRoles", 
            "Sites.Contacts"
        ]
    }, 
    { 
        "$project" : { 
            "Sites.SiteId" : 1.0
        }
    }
], 
{ 
    "allowDiskUse" : true
}

This gives me the following result

{ 
    "_id" : ObjectId("617c95759b189684f820f1ff"), 
    "Sites" : {
        "SiteId" : "B35AA462-81F1-4C56-8569-5EC34858B943"
    }
}
{ 
    "_id" : ObjectId("617c95759b189684f820f1ff"), 
    "Sites" : {
        "SiteId" : "953B5A1D-84E3-4660-93B4-8CB2F8FB5849"
    }
}
{ 
    "_id" : ObjectId("617c95759b189684f820f1ff"), 
    "Sites" : {
        "SiteId" : "0D639A37-1812-4CFC-88FF-E5820F3C078D"
    }
}
{ 
    "_id" : ObjectId("617c95759b189684f820f1ff"), 
    "Sites" : {
        "SiteId" : "8AB3C74D-77E6-4749-997C-90CB08430B4D"
    }
}
{ 
    "_id" : ObjectId("617c95759b189684f820f1ff"), 
    "Sites" : {
        "SiteId" : "F0917AC3-6618-4BA4-9A3D-48212E28D3D2"
    }
}
{ 
    "_id" : ObjectId("617c95759b189684f820f1ff"), 
    "Sites" : {
        "SiteId" : "922A081C-30E3-4FCA-A911-EBB277771BD5"
    }
}
{ 
    "_id" : ObjectId("617c95759b189684f820f1ff"), 
    "Sites" : {
        "SiteId" : "8B1989C6-A30F-4C6C-9AF0-BB100FA49075"
    }
}
{ 
    "_id" : ObjectId("617c95759b189684f820f1ff"), 
    "Sites" : {
        "SiteId" : "6CC9407E-EBBB-42CE-81CE-B33F9DB16472"
    }
}
{ 
    "_id" : ObjectId("617c95759b189684f820f1ff"), 
    "Sites" : {
        "SiteId" : "8A2719B0-CB2A-4CDD-925F-3A47958A47D6"
    }
}

Which is actually right but I just want basically an array result listing the “SiteId” not a “Sites” Property on each element which I understand why that is happening but I don’t know how to project it correctly.

thanks for any help again pretty new to all this, so trying to do a bit of a crash course using the 3T studio, which I’m starting to understand and is very helpful, it got me to this point.

thanks
Michael

2

Hello @Michael_Pine, you can simply substitute the (above) last two stages with this:

{ $project: { siteId: "$Sites.SiteId" } }

In case you don’t want the _id field in the output, you can do this:

{ $project: { _id: 0, siteId: "$Sites.SiteId" } }

3

Hi,

thanbs so much for the reply and yep that is exactly what I want, thank you very much, so I had the path expression right I just hadn’t worked out you could put the field name in there as well, thanks so much!

cheers
Michael