Mappings

Mappings allow you to logically connect collections to help the AI to acknowledge $lookup support on the mapped fields.

How it works

You can map a key to other keys inside other collections to let the AI know that collections can be joined via $lookup operations on these mapped fields.

Example: Map keys in a real estate renting database

Imagine a rentals database that has 4 collections:

Now we want to interconnect these collections by mapping the following fields:

Connection: Each flat belongs to a specific house.
Mapping: Map _id from houses collection to houseId in flats collection

Connection: Each flat accomodates one or more tenants.
Mapping: Map _id from tenants collection to tenantIds in flats collection

Connection: Each tenant has a monthly rent to pay.
Mapping: Map _id from tenants collection to tenantId in incomes collection

Connection: Each flat has an overall monthly rent paid by all tenants living there.
Mapping: Map tenantId from incomes collection to tenantIds in flats collection

Create Mappings

To start creating mappings, click on Database Settings in the upper right corner of the collections overview and navigate to the Key Mappings tab:

You always create a mapping by first selecting the Key path you want to map from:

Then a list of all collections are shown that include this keyPath. Select the collection(s) you want to map that Key path from:

Finally select the Key path and collection(s) to map to and click Save:

Please note

You can select multiple collections to map different collections at once.

I.e. a very common case would be that you want to map the _id of a users collection to a userId in multiple other collections.

For our example rentals collection the final mapping table would look like this:

Ask example question using all mappings

Now we can ask this question that needs to make use of all 4 collections in order to be answered correct:

Who lives in the Sesame Street on the ground floor and how much overall rent do the tenants there pay?

Thanks to the mapping, the question results in the following quite complex, but correct aggregation, result and answer:

Aggregation

{
  "database": "rentals",
  "collection": "flats",
  "aggregation": [
    {
      "$lookup": {
        "from": "houses",
        "localField": "houseId",
        "foreignField": "_id",
        "as": "house"
      }
    },
    {
      "$match": {
        "floor": "Ground floor",
        "house.name": "Sesame Street"
      }
    },
    {
      "$unwind": "$tenantIds"
    },
    {
      "$group": {
        "_id": "$tenantIds"
      }
    },
    {
      "$lookup": {
        "from": "tenants",
        "let": {
          "tenantId": "$_id"
        },
        "pipeline": [
          {
            "$match": {
              "$expr": {
                "$eq": ["$_id", "$$tenantId"]
              }
            }
          },
          {
            "$project": {
              "name": 1
            }
          }
        ],
        "as": "tenantInfo"
      }
    },
    {
      "$lookup": {
        "from": "incomes",
        "let": {
          "tenantId": "$_id"
        },
        "pipeline": [
          {
            "$match": {
              "$expr": {
                "$eq": ["$tenantId", "$$tenantId"]
              }
            }
          },
          {
            "$group": {
              "_id": null,
              "totalRent": {
                "$sum": "$amount"
              }
            }
          }
        ],
        "as": "incomeInfo"
      }
    },
    {
      "$addFields": {
        "tenantName": {
          "$arrayElemAt": ["$tenantInfo.name", 0]
        },
        "totalRent": {
          "$ifNull": [
            {
              "$arrayElemAt": ["$incomeInfo.totalRent", 0]
            },
            0
          ]
        }
      }
    },
    {
      "$project": {
        "_id": 0,
        "tenantId": "$_id",
        "tenantName": 1,
        "totalRent": 1
      }
    }
  ]
}

Result

[
  {
    "tenantName": "Ernie",
    "totalRent": 4740.53,
    "tenantId": "64f1e8c1e4b0c3f1a4e1a1a5"
  },
  {
    "tenantName": "Bert",
    "totalRent": 3600,
    "tenantId": "64f1e8c1e4b0c3f1a4e1a1a2"
  }
]

Answer

The ground floor is occupied by the tenants Ernie and Bert. Together, they pay a total overall rent of approximately 8340,53.