How to deal with date range using Atlas searchMeta

[ahmad_al_sharbaji (Ahmad)]

I have documents like this sample:

{
    "_id": {"$oid": "64e6216ba465960d9107323f"},
    "log": {
        "created_by": "303feff3-0d01-4322-8ebe-ee27d55be2df",
        "update_count": 1,
        "updated_at": {"$date": "2023-08-23T15:10:47.537Z"},
        "updates_log": [
            {
                "updated_at": {"$date": "2023-08-23T15:10:47.537Z"},
                "updated_by": "303feff3-0d01-4322-8ebe-ee27d55be2df",
                "new_state": {"msg": "Example Message X"},
                "original_state": {"msg": "Example Message"},
            }
        ],
    },
    "cnf": {"st": 1},
}

I already have this aggregation to count documents based on cnf.st and log.updated_at for the last 24 hours (means the documents updated in the last 24 hours).

Aggregation:

result = message_collection.aggregate(
[
            {"$match": {"cnf.st": {
                "$in": [1, -4]}}},
            {
                "$facet": {
                    "updated_events_last_24": [
                        {"$match": {"log.update_count": {"$gt": 0},
                                    "log.updated_at": {"$gte": QueryParams.get_last_24()}}},
                        {"$group": {"_id": {}, "count": {"$sum": 1}}},
                    ],
                }
            }
        ]
)

list(result)

Now I’m trying to apply it using Atlas searchMeta approach like below:

result = message_collection.aggregate(
    [
        {
            "$searchMeta": {
                "index": "MsgAtlasIndex",
                "count": {"type": "total"},
                "compound": {
                    "must": [
                        {
                            "range": {"path": "log.update_count", "gt": 0}
                        },
                        {
                            "range": {"path": "log.updated_at", "gte": QueryParams.get_last_24()}
                        },
                        {
                            "in": {
                                "path": "cnf.st",
                                "value": [1, -4]
                            }
                        },
                    ]
                }
            }
        }
    ]
)

list(result)

But I always getting count 0.

Any idea please?

To get the time within the last 24 hours used above:

from datetime import datetime, timedelta
class QueryParams:
    """Query params."""

    @staticmethod
    def get_last_24() -> datetime:
        """Pattern for time: 24 hours."""
        return datetime.now() - timedelta(hours=24)

[slava]

Hello, @ahmad_al_sharbaji !

I tried to run your queries against the data you provided - everything works on my side .

Make sure you correctly specified Atlas Search indexes for the fields you are using in your pipeline with $searchMeta.

To support this given query, your “MsgAtlasIndex” should be similar to this:

{
  "mappings": {
    "dynamic": false,
    "fields": {
      "cnf": {
        "fields": {
          "st": {
            "type": "number"
          }
        },
        "type": "document"
      },
      "log": {
        "fields": {
          "update_count": {
            "type": "number"
          },
          "updated_at": {
            "type": "date"
          }
        },
        "type": "document"
      }
    }
  }
}

Simplified version of your dataset, that I used:

db.myLogs.insertMany([
  {
    _id: 'A',
    log: {
      update_count: 1,
      updated_at: ISODate('2023-08-23T15:00:00.000Z'), // 15:00
    },
    cnf: {
      st: 1
    },
  },
  {
    _id: 'B',
    log: {
      update_count: 2,
      updated_at: ISODate('2023-08-23T12:00:00.000Z'), // 12:00
    },
    cnf: {
      st: 2
    },
  },
  {
    _id: 'C',
    log: {
      update_count: 1,
      updated_at: ISODate('2023-08-23T15:15:00.000Z'), // 15:15
    },
    cnf: {
      st: 1
    },
  }
]);

Pipeline with $searchMeta I used:

db.myLogs.aggregate([
  {
    $searchMeta: {
      index: 'logs-search-test',
      count: {
        type: 'total'
      },
      compound: {
        must: [
          {
            range: {
              path: 'log.update_count',
              gt: 0
            },
          },
          {
            range: {
              path: 'log.updated_at',
              gte: ISODate('2023-08-23T15:00:00.000Z') // 15:00
            }
          },
          {
            in: {
              path: 'cnf.st',
              value: [-4, 1]
            }
          }
        ]
      },
    },
  },
]);

Output:

// Documents A and C
{ count: { total: Long("2") } } ]

Let me know, if this helped

[ahmad_al_sharbaji (Ahmad)]

@slava Exactly. My query was correct and It was the index definition. Thank you, Beast .