MongoDB add field with keys from object

Question

I have the following two collections:

{
  "organizations": [
      {
        "_id": "1",
        "name": "foo",
        "users": { "1": "admin", "2": "member" }
      },
      {
        "_id": "2",
        "name": "bar",
        "users": { "1": "admin" }
      }
  ],
  "users": [
      {
          "_id": "1",
          "name": "john smith"
      },
      {
          "_id": "2",
          "name": "bob johnson"
      }
  ]
}

The following query works to merge the users into members when I just use an array of the user ids to match, however, the users prop is an object.

{
    "collection": "organizations",
    "command": "aggregate",
    "query": [
        {
            "$lookup": {
                "from": "users",
                "localField": "users",
                "foreignField": "_id",
                "as": "members"
            }
        }
    ]
}

What I'm hoping to do is lookup by id then create a members array from the results with the user object including the role (value of the users objects:

{
  "_id": "1",
  "name": "foo",
  "users": {
      "1": "admin",
      "2": "member"
  },
  "members": [
    {
      "_id": "1",
      "name": "john smith",
      "role": "admin"
    },
    {
      "_id": "2",
      "name": "bob johnson",
      "role": "user"
    }
  ]
}

Here's the sandbox I have setup: https://mongoplayground.net/p/yhRpeRvJf3u

turivishal · Accepted Answer · 2020-11-24 17:50:32Z

1

You really need to change your schema design, this will cause the performance on retrieving data,

$addFields to add new field usersArray convert users object to array using $objectToArray, the format will be k(key) and v(value),
$lookup to join users collection, set localField name to usersArray.k
$addFields, remove usersArray field using $$REMOVE,
$map iterate loop of members array and $reduce to iterate loop of usersArray and get matching role as per _id and merge current fields and role field using $mergeObjects

db.organizations.aggregate([
  {
    $addFields: {
      usersArray: {
        $objectToArray: "$users"
      }
    }
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "usersArray.k",
      "foreignField": "_id",
      "as": "members"
    }
  },
  {
    $addFields: {
      usersArray: "$$REMOVE",
      members: {
        $map: {
          input: "$members",
          as: "m",
          in: {
            $mergeObjects: [
              "$$m",
              {
                role: {
                  $reduce: {
                    input: "$usersArray",
                    initialValue: "",
                    in: { $cond: [{ $eq: ["$$this.k", "$$m._id"] }, "$$this.v", "$$value"] }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground

answered Nov 24, 2020 at 17:50

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fluidbyte Over a year ago

Thanks! I know the schema isn't great, I'm working with a (startup) project that's really had some changes to business needs. 95% of our schemas are in really good shape and are minimally (if at all) related to other collections.

J.F. · Accepted Answer · 2020-11-24 17:56:48Z

First of all, the problem with your query is you want to use a KEY to do the $lookup, then the members field always gonna be empty.

You are trying to use users as local field, but users is an object, so you need the key (users.1, users.2, ... )

To do this you need to use $objectToArray, which create an object array with two fields: k and v for key and value. So now, you can $lookup with the field users.k.

To get the query you need $unwind before $lookup because you also want the users filed into the new document.

With the new object created using $objectToArray, you can do $unwind to get the values in differents documents. And then $lookup to get the "join".
Here, localField uses the value k created by $objectToArray (the object key).

After that, $set to add the field with the role and $group again into one document.

Ive used _id to get the values without changes between stages, and into members push the members in each collection.

And then, $project to output the values you want. In this case, tha calues "stored" into _id and the array members in "one level" using $reduce.

So, the query you need I think is this:

db.organizations.aggregate([
  {
    "$match": {
      "_id": "1"
    }
  },
  {
    "$set": {
      "usersArray": {
        "$objectToArray": "$users"
      }
    }
  },
  {
    "$unwind": "$usersArray"
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "usersArray.k",
      "foreignField": "_id",
      "as": "members"
    }
  },
  {
    "$set": {
      "members.role": "$usersArray.v"
    }
  },
  {
    "$group": {
      "_id": {
        "_id": "$_id",
        "users": "$users",
        "name": "$name"
      },
      "members": {
        "$push": "$members"
      }
    }
  },
  {
    "$project": {
      "members": {
        "$reduce": {
          "input": "$members",
          "initialValue": [],
          "in": {
            "$concatArrays": [
              "$$value",
              "$$this"
            ]
          }
        }
      },
      "users": "$_id.users",
      "name": "$_id.name",
      "_id": "$_id._id"
    }
  }
])

Example here

Collectives™ on Stack Overflow

MongoDB add field with keys from object

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related