Behaviour of $addFields after $unwind

Working with Data
1

Hi,

I have the following data:

db={
  "accounts": [
    {
      "_id": {
        "$oid": "644798f79ad5e97dd4296152"
      },
      "name": "The James Trust",
      "schools:": [
        {
          "_id": {
            "$oid": "6447998d9ad5e97dd429615a"
          },
          "name": "Jamestown Academy"
        },
        {
          "_id": {
            "$oid": "644799d99ad5e97dd4296161"
          },
          "name": "Jamestown College"
        }
      ]
    }
  ],
  "users": [
    {
      "_id": {
        "$oid": "63e27b3483161a984f19fcce"
      },
      "accountId": {
        "$oid": "644798f79ad5e97dd4296152"
      },
      "name": "James Boing",
      "email": "james@whatever.boing",
      "schools": [
        {
          "roles": [
            "admin",
            "safeguard"
          ],
          "schoolId": {
            "$oid": "6447998d9ad5e97dd429615a"
          }
        },
        {
          "roles": [
            "staff"
          ],
          "schoolId": {
            "$oid": "644799d99ad5e97dd4296161"
          }
        }
      ]
    }
  ]
}

I don’t seem to be able to use the $account.schools reference in the following example:

db.users.aggregate([
  {
    $match: {
      _id: ObjectId("63e27b3483161a984f19fcce")
    }
  },
  {
    $lookup: {
      from: "accounts",
      localField: "accountId",
      foreignField: "_id",
      as: "account"
    }
  },
  {
    $unwind: {
      path: "$account"
    }
  },
  {
    $addFields: {
      z: {
        $arrayElemAt: [
          "$account.schools",
          0
        ]
      }
    }
  }
])

Instead, I always get null for z. I think I’m missing something fundamental here in the way that $unwind is operating. I’m only using $unwind to flatten the array because it’s a 1-1 relationship between a user and an account.

I’ve been stuck for hours on this, and any help would be appreciated.

2

Hi @James_N_A2,

I think this might be the issue here above (based off the sample data at least). The field name here (for the accounts collection) is "schools:" as opposed to "schools".

I managed to replicate what you were experiencing:

[
  {
    _id: ObjectId("63e27b3483161a984f19fcce"),
    accountId: ObjectId("644798f79ad5e97dd4296152"),
    name: 'James Boing',
    email: 'james@whatever.boing',
    schools: [
      {
        roles: [ 'admin', 'safeguard' ],
        schoolId: ObjectId("6447998d9ad5e97dd429615a")
      },
      {
        roles: [ 'staff' ],
        schoolId: ObjectId("644799d99ad5e97dd4296161")
      }
    ],
    account: {
      _id: ObjectId("644798f79ad5e97dd4296152"),
      name: 'The James Trust',
      'schools:': [
        {
          _id: ObjectId("6447998d9ad5e97dd429615a"),
          name: 'Jamestown Academy'
        },
        {
          _id: ObjectId("644799d99ad5e97dd4296161"),
          name: 'Jamestown College'
        }
      ]
    },
    z: null
  }
]

Output ater changing the value from "$account.schools" to "$account.schools:" in the $addFields stage:

[
  {
    _id: ObjectId("63e27b3483161a984f19fcce"),
    accountId: ObjectId("644798f79ad5e97dd4296152"),
    name: 'James Boing',
    email: 'james@whatever.boing',
    schools: [
      {
        roles: [ 'admin', 'safeguard' ],
        schoolId: ObjectId("6447998d9ad5e97dd429615a")
      },
      {
        roles: [ 'staff' ],
        schoolId: ObjectId("644799d99ad5e97dd4296161")
      }
    ],
    account: {
      _id: ObjectId("644798f79ad5e97dd4296152"),
      name: 'The James Trust',
      'schools:': [
        {
          _id: ObjectId("6447998d9ad5e97dd429615a"),
          name: 'Jamestown Academy'
        },
        {
          _id: ObjectId("644799d99ad5e97dd4296161"),
          name: 'Jamestown College'
        }
      ]
    },
    z: {
      _id: ObjectId("6447998d9ad5e97dd429615a"),
      name: 'Jamestown Academy'
    }
  }
]

Regards,
Jason

2 Likes
3

Nice catch. When they say the devil hides in the details. This is what they mean.

1 Like
4

I can’t believe I missed that :shame:. Thank you for your eagle eyes!

1 Like
5

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.