Views

"Views" allow defining a data processing pipeline from Entities that will be invoked every time the View is accessed.

Key Characteristics

• Data Processing: Views allow for Entity processing, grouping, joining and transformation.
• Querying: Views can be queried using a mongo-like query language like Entities.
• Belong to Entity: View always belongs to an Entity.

Use Cases

Views can be very powerful tools for managing and querying data in complex systems. Here are a couple of use cases for using Views:

• Data Aggregation for Reporting: Views can be used to aggregate data from multiple collections or documents to create summarized views suitable for reporting and analysis. For instance, you could create a view that aggregates customer sales data across various regions and groups them by product category. This would allow for easy reporting on sales performance without running complex queries on the entire dataset each time.
• Simplified Data Access for Application Users: Views can simplify the access to data for application users by pre-filtering and transforming data according to specific user roles or requirements. For example, if you have a database storing detailed user activity logs, you could create a view that only exposes the necessary fields like activity type, date, and user ID. This prevents exposure of sensitive data and reduces the amount of data transferred and processed by the application.

Create View

To create a View 1) Open View Tab in selected Entity 2) Open Create View window with "Create View" button 3) Fill in the following fields:

• Name: Name of the view. String from 3 to 64 characters
• Pipeline: The pipeline is a sequence of stages that define the processing steps for the view. Each stage can perform operations like filtering, grouping, sorting, and projecting data. The pipeline is executed in the order of the stages listed. The documents that are output from a stage are passed to the next stage. A pipeline can return results for groups of documents. For example, return the total, average, maximum, and minimum values. BuiltAPI uses the MongoDB aggregation pipeline syntax for defining the stages. You can find more information about the MongoDB aggregation pipeline here.

View example

This section provides view examples that use "transactions" Entity with following records:

[
    {
        "transaction_id": "txn001",
        "amount": 150.00,
        "transaction_date": "2024-04-01",
        "category": "Electronics",
        "customer_id": "cust01"
    },
    {
        "transaction_id": "txn002",
        "amount": 200.00,
        "transaction_date": "2024-04-02",
        "category": "Clothing",
        "customer_id": "cust02"
    },
    {
        "transaction_id": "txn003",
        "amount": 75.00,
        "transaction_date": "2024-04-01",
        "category": "Electronics",
        "customer_id": "cust02"
    },
    {
        "transaction_id": "txn004",
        "amount": 100.00,
        "transaction_date": "2024-04-03",
        "category": "Groceries",
        "customer_id": "cust03"
    }
]

The "transactions" Entity contains the following fields:

• transaction_id — The unique identifier for the transaction.
• amount — The monetary amount of the transaction.
• transaction_date — The date the transaction was conducted.
• category — The category of the purchase, which can be used for grouping data.
• customer_id — A reference to record in another Entity that contains information about customers.

Calculate total amount of transactions grouped by customer for specific category

1) Create new View

• name - "total-amount-of-electronics-by-customer"
• pipeline

[
  {
    "$match": {
      "data.category": "Electronics"
    }
  },
  {
    "$group": {
      "_id": "$data.customer_id",
      "total_amount": {
        "$sum": "$data.amount"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "category": "Electronics",
      "customer": "$_id",
      "total_rent": 1
    }
  },
  {
    "$sort": {
      "total_rent": -1
    }
  }
]

This pipeline consists of four stages, filtering by the category field, grouping by customer_id, and then reshaping the output:

• $match Stage:

  Filters the documents to include only those where the data.category field is "Electronics". This stage reduces the dataset to relevant documents that match the specified criteria.

• $group Stage:

  Groups the filtered documents by the data.customer_id, calculates the total sum of data.amount for each group and stores this total in a field named total_rent. This aggregation sums up all the transaction amounts for each customer in the Electronics category.

• $project Stage:

  Transforms the output documents to include only the desired fields:

  • Excludes the _id field by setting _id: 0.
  • Includes a category field with the static value "Electronics".
  • Renames the _id field (which holds the customer ID from the $group stage) to customer.
  • Includes the total_rent field which contains the computed sum from the $group stage.

• $sort Stage:

  Sorts the output documents by the total_rent field in descending order. This stage ensures that the results are ordered by the total rent amount, with the highest total rent appearing first.

Average amount of transaction by customer with customer name

In our example "transactions" Entity contains a reference to another Entity "customers" that stores customer information. The "customers" Entity contains the following records:

[
    {
        "id": "cust01",
        "name": "Alice"
    },
    {
        "id": "cust02",
        "name": "Bob"
    },
    {
        "id": "cust03",
        "name": "Charlie"
    }
]

To add the customer name to our new view records, we can use the $lookup stage to join the "transactions" and "customers" Entities based on the customer_id field. Let's create new view

• name - "average-amount-of-transaction-by-customer"
• pipeline

[
  {
    "$group": {
      "_id": "$data.customer_id",
      "total_amount": {
        "$avg": "$data.amount"
      }
    }
  },
  {
    "$lookup": {
      "from": "75e04a10-afa0-4514-ba02-2ea82aff122d",
      "localField": "_id",
      "foreignField": "data.id",
      "as": "customer"
    }
  },
  {
    "$unwind": "$customer"
  },
  {
    "$project": {
      "_id": 0,
      "customer_id": "$customer.data.id", 
      "customer_name": "$customer.data.name",
      "total_rent": 1
    }
  },
  {
    "$sort": {
      "total_rent": -1
    }
  }
]

We add two new stages:

• "lookup" that joins the "transactions" and "customers" Entities based on the customer_id field.
• "unwind" that deconstructs the customer array created by the $lookup stage to flatten the output.

The lookup stage has the following options:

• from: The id of the Entity to join with. In this case, we use the id of the "customers" Entity. We can't use the name of the Entity because the name can be changed.
• localField: The field from the input documents that holds the value to match against the foreignField. In our case, this is the _id field from the $group stage.
• foreignField: The field from the documents in the "from" collection that holds the value to match against the localField.
• as: The name of the new array field that will hold the joined documents. In this case, we use the name "customer".

The unwind stage is used to flatten the customer array created by the $lookup stage. This stage is necessary because the $lookup stage creates an array of joined documents, and we want to access the fields of the joined document directly in the subsequent stages.

In result, the output of the view will contain the customer name instead of the customer_id.

[
    {
        "category": "Electronics",
        "customer": "Alice",
        "total_rent": 150.00
    },
    {
        "category": "Electronics",
        "customer": "Bob",
        "total_rent": 75.00
    }
]