Indexing is not applying when I used $regex in My aggregate pipeline.

GANESHKUMAR_ALLIMUTHU · 2024-06-11T17:52:18.583Z

[
  {
    $sort: {
      _id: -1,
    },
  },
  {
    $lookup: {
      from: "SalesHub_Contacts",
      localField: "contactid",
      foreignField: "contactid",
      as: "contactid",
    },
  },
  {
    $addFields: {
      contactid: {
        $arrayElemAt: ["$contactid", 0],
      },
    },
  },
  {
    $addFields: {
      contactid: {
        $concat: [
          {
            $arrayElemAt: [
              [
                "$contactid.contactid",
                "$contactid.firstname",
                "$contactid.lastname",
              ],
              0,
            ],
          },
          {
            $reduce: {
              input: [
                {
                  $concat: [
                    " (",
                    "$contactid.firstname",
                  ],
                },
                {
                  $concat: [
                    ", ",
                    "$contactid.lastname",
                  ],
                },
              ],
              initialValue: "",
              in: {
                $concat: ["$$value", "$$this"],
              },
            },
          },
          {
            $cond: {
              if: {
                $gt: [
                  {
                    $size: {
                      $ifNull: [
                        [
                          {
                            $concat: [
                              " (",
                              "$contactid.firstname",
                            ],
                          },
                          {
                            $concat: [
                              ", ",
                              "$contactid.lastname",
                            ],
                          },
                        ],
                        [],
                      ],
                    },
                  },
                  0,
                ],
              },
              then: ")",
              else: "",
            },
          },
        ],
      },
    },
  },
  {
    $lookup: {
      from: "SalesHub_Accounts",
      localField: "accountid",
      foreignField: "accountid",
      as: "accountid",
    },
  },
  {
    $addFields: {
      accountid: {
        $arrayElemAt: ["$accountid", 0],
      },
    },
  },
  {
    $addFields: {
      accountid: {
        $concat: [
          {
            $arrayElemAt: [
              [
                "$accountid.accountid",
                "$accountid.accountname",
              ],
              0,
            ],
          },
          {
            $reduce: {
              input: [
                {
                  $concat: [
                    " (",
                    "$accountid.accountname",
                  ],
                },
              ],
              initialValue: "",
              in: {
                $concat: ["$$value", "$$this"],
              },
            },
          },
          {
            $cond: {
              if: {
                $gt: [
                  {
                    $size: {
                      $ifNull: [
                        [
                          {
                            $concat: [
                              " (",
                              "$accountid.accountname",
                            ],
                          },
                        ],
                        [],
                      ],
                    },
                  },
                  0,
                ],
              },
              then: ")",
              else: "",
            },
          },
        ],
      },
    },
  },

  {
    $match: {
      role: {
        $regex: "^dire",
        $options: "i",
      },
     
    },
  },
].

This is my query. I am trying to lookup a feild and modify the content of the forign feild. and I am trying to make search with that using $match and $regex. I created a index too. Not working.

John_Page · 2024-07-01T14:38:16.176Z

H I cannot work out from just the code you have posted what you are trying to to, can you please just post a minimal example of what you are trying to do, a couple of small example documnts and the query/modification you want to do. We cannot diagnose issues with your code if we don’t know what iut’s trying to do.