Project field of array element

[Andriy_Simonov]

Hi everyone,

I am working with the following document structure in MongoDB:

{
  "_id": ObjectId("667f0e313c6b46abdcb4d8ff"),
  "value": 32,
  "versioned": [
    {
      "tenants": [
        {"_id": ObjectId("66814426dc4732821d1e883d")},
        {"_id": ObjectId("66814426dc4732821d1e883e")}
      ]
    },
    {
      "tenants": [
        {"_id": ObjectId("66814426dc4732821d1e883d")},
        {"_id": ObjectId("66814426dc4732821d1e883e")},
        {"_id": ObjectId("66854d67fe43fed588a6c8be")}
      ],
      "balance": -128
      },
    {
      "tenants": [
        {"_id": ObjectId("66814426dc4732821d1e883d")}
      ],
      "balance": -256
      }
    }
    }
  ]
}

I aim to project the balance and tenants fields from the versioned array element at index 1. For this, I use the following aggregation pipeline:

[
  {
    $match: {
      _id: ObjectId("667f0e313c6b46abdcb4d8ff")
    }
  },
  {
    $project: {
      tenants: {$arrayElemAt: ["$versioned.tenants", 1]},
      balance: {$arrayElemAt: ["$versioned.balance", 1]}
    }
  }
]

However, the result I get is:`

{
  "_id": ObjectId("667f0e313c6b46abdcb4d8ff"),
  "balance": -256,
  "tenants": [
    {"_id": ObjectId( "66814426dc4732821d1e883d")},
    {"_id": ObjectId("66814426dc4732821d1e883e")},
    {"_id": ObjectId("66854d67fe43fed588a6c8be")}
  ]
}

In this result, the tenants are correctly fetched from the element at index 1, but the balance is fetched from the element at index 2.

If I change the projection to the following:

{
    $project: {
      tenants: {$arrayElemAt: ["$versioned.tenants", 0]},
      balance: {$arrayElemAt: ["$versioned.balance", 0]}
    }
}

I get this result:`

{
  "_id": {
    "$oid": "667f0e313c6b46abdcb4d8ff"
  },
  "balance": -128,
  "tenants": [
    {"_id": ObjectId("66814426dc4732821d1e883d")},
    {"_id": ObjectId("66814426dc4732821d1e883e")}
  ]
}

Here, tenants is fetched from the element at index 0, and balance is fetched from the element at index 1. It appears that balance is not present in the element at index 0, and thus the field is being indexed independently.

I have not found any documentation or references online that explain this behavior. Is this a feature or a bug? Any insights or pointers to relevant documentation would be greatly appreciated.

[turivishal (Vishal Turi)]

Hello @Andriy_Simonov,

The first element does not have a balance field which is why "$versioned.balance" will return [-128, -256], and as you can see what is the 0th element and what is the 1st.

You can try something like this:

  {
    $project: {
      tenants: {
        $getField: {
          field: "tenants",
          input: { $arrayElemAt: ["$versioned", 1] }
        }
      },
      balance: {
        $getField: {
          field: "balance",
          input: { $arrayElemAt: ["$versioned", 1] }
        }
      }
    }
  }

[Andriy_Simonov]

Thank you for your help! Your solution of using $getField perfectly resolved my issue. I really appreciate your time and expertise.