Return 2D Array

Hi everyone, I have a question that I need some help with.

I have some data stored in a Collection like so:

... // just the important part of the document that will be used in the query
groups: {
 group1: ['userId1', 'userId2'], //array of user ID's
 group2: ['userId3', 'userId4'],
 group3: ['userId5', 'userId6'],
}

Is it possible to return the data from the Users collection formatted like this:

[
  group1: [{_id: userId1, profile: {}}, {_id: userId2, profile: {}}],
  group2: [{_id: userId3, profile: {}}, {_id: userId4, profile: {}}],
  group3: [{_id: userId5, profile: {}}, {_id: userId6, profile: {}}],
]

OR

like this:

[
  [{_id: userId1, profile: {}}, {_id: userId2, profile: {}} /*more users in this group*/ ],
  [{_id: userId3, profile: {}}, {_id: userId4, profile: {}}],
  [{_id: userId5, profile: {}}, {_id: userId6, profile: {}}],
]

Any help or advice would be greatly appreciated.

Thanks.

Hi @Dev_Ops,

This can be accomplished by using $lookup (for a single group). The problem is not to join Users (full doc) by userId, but joining in each group inside groups. $lookup uses 1 key to lookup from other collection. If you want this, you would have to remodel your data (groups collections) in something like this -

{
    "group": { // 1 document per group, and later on you can use $group aggregation to group by these groups.
        "users": ["userId1", "userId2"], 
        "groupId": "1"
    }
}

OR

{
    "group": [
        {
            "groupId": "group1",
            "user": "user1"
        },
        {
            "groupId": "group1",
            "user": "user1"
        }
    ]
}

Please note - Having dynamic “keys” in your data model is a very bad practise and you should try to avoid such cases. These ways, you know exactly which keys to $lookup on and later on you can use $group/$project to modify your query result.

1 Like

Thanks @shrey_batra, I will give this a shot.

Hello

As being said ,having unknown keys ,is not good idea in mongo.
Arrays are better for dynamic data.

But this query does what you want using $objectToArray and $map

It produces the second way that you said its ok.

{
  "aggregate": "testcoll",
  "pipeline": [
    {
      "$project": {
        "_id": 0,
        "groupsArray": {
          "$let": {
            "vars": {
              "groups": {
                "$map": {
                  "input": {
                    "$map": {
                      "input": {
                        "$objectToArray": "$groups"
                      },
                      "as": "m",
                      "in": [
                        "$$m.k",
                        "$$m.v"
                      ]
                    }
                  },
                  "as": "group",
                  "in": {
                    "$map": {
                      "input": {
                        "$arrayElemAt": [
                          "$$group",
                          1
                        ]
                      },
                      "as": "userid",
                      "in": {
                        "_id": "$$userid",
                        "profile": {}
                      }
                    }
                  }
                }
              }
            },
            "in": "$$groups"
          }
        }
      }
    }
  ],
  "cursor": {},
  "maxTimeMS": 1200000
}

Same query , just printed more concise,but its not valid json, : and , are missing

{"aggregate" "testcoll",
 "pipeline"
 [{"$project"
   {"_id" 0,
    "groupsArray"
    {"$let"
     {"vars"
      {"groups"
       {"$map"
        {"input"
         {"$map"
          {"input" {"$objectToArray" "$groups"},
           "as" "m",
           "in" ["$$m.k" "$$m.v"]}},
         "as" "group",
         "in"
         {"$map"
          {"input" {"$arrayElemAt" ["$$group" 1]},
           "as" "userid",
           "in" {"_id" "$$userid", "profile" {}}}}}}},
      "in" "$$groups"}}}}],
 "cursor" {},
 "maxTimeMS" 1200000}

Thanks @Takis, missed your response for some reason.

Is there any performance benefit to your option versus @shrey_batra