Jul 2024
4 / 4
Jul 2024
Jul 2024
Jose_Olaizola
Jul 2024

Hello, I’m using Mongodb Compass v1.41.0, and would like to know if there is a way to use and define variables on aggregations tab, I created a script for data analysis and uses some values multiple times, sometimes I need to change those values, and is annoying to change these on all stages, is there any way to use variables or any idea that may solve the issue? Thanks in advance

image

steevejSteeve Juneau
Jul 2024

We could help better if you share your aggregation. But without knowing more I can point you to $let which would be appropriate in some cases.

Another way it to use $addField to set temporary fields that you could use later as any other fields.

Jose_Olaizola
Jul 2024

oh sorry, here. I would like to create variables for “Entry Date” and “Source” fields

[
  {
    $match:
      /**
       * query: The query in MQL.
       */
      {
        Source: "Clientx",
        "Entry Date": {
          $gte: ISODate(
            "2024-06-01T04:00:00.000+00:00"
          ),
          $lte: ISODate(
            "2024-07-01T04:00:00.000+00:00"
          ),
        },
      },
  },
  {
    $facet:
      /**
       * outputFieldN: The first output field.
       * stageN: The first aggregation stage.
       */
      {
        TotalClicks: [
          {
            $group: {
              _id: "$Source",
              TotalClicks: {
                $sum: 1,
              },
              TotalCost: {
                $sum: "$Cost",
              },
            },
          },
        ],
        UniqueIps: [
          {
            $group: {
              _id: "$IP Address",
            },
          },
          {
            $count: "UniqueIps",
          },
        ],
      },
  },
  {
    $unwind:
      /**
       * path: Path to the array field.
       * includeArrayIndex: Optional name for index.
       * preserveNullAndEmptyArrays: Optional
       *   toggle to unwind null and empty values.
       */
      {
        path: "$TotalClicks",
      },
  },
  {
    $unwind:
      /**
       * path: Path to the array field.
       * includeArrayIndex: Optional name for index.
       * preserveNullAndEmptyArrays: Optional
       *   toggle to unwind null and empty values.
       */
      {
        path: "$UniqueIps",
      },
  },
  {
    $project:
      /**
       * specifications: The fields to
       *   include or exclude.
       */
      {
        Source: "$TotalClicks._id",
        Total_Cost: "$TotalClicks.TotalCost",
        Total_Clicks: "$TotalClicks.TotalClicks",
        Unique_Clicks: "$UniqueIps.UniqueIps",
        Duplicate_Rate: {
          $concat: [
            {
              $toString: {
                $multiply: [
                  {
                    $round: [
                      {
                        $divide: [
                          {
                            $subtract: [
                              "$TotalClicks.TotalClicks",
                              "$UniqueIps.UniqueIps",
                            ],
                          },
                          "$TotalClicks.TotalClicks",
                        ],
                      },
                      4,
                    ],
                  },
                  100,
                ],
              },
            },
            "%",
          ],
        },
      },
  },
]

Want to read more? Browse other topics in Other MongoDB Topics or view latest topics.