Convert the MongoDB query to Opensearch

Elastic Stack Elasticsearch
1

I have the aggregation query in mongoDB which is working properly. THis query finds the count of the users hour-wise but only consider the first arrival of the user. For example if user arrives at 11 AM and then again at 2 PM, then count will be 1 for this user at 11 AM and that's it. If the same user comes again then count should not be calculated. for simplicity we can say hour-wise count of the first arrival of the user.

This is the mongoDB query, I am using the variables as it is form the mongoDB code

aggregate([
            {
                $match: {
                    sequr_code: { $in: sequr_code },
                    'actor.user.uuid': { $in: valid_users },
                    time: { $gt: start_date, $lte: end_date },
                    'location.uuid': location_id,
                },
            },
            {
                $sort: {
                    time: 1,
                },
            },
            {
                $group: {
                    _id: {
                        uuid: '$actor.user.uuid',
                    },
                    tran: { $first: '$$ROOT' },
                },
            },
            {
                $group: {
                    _id: {
                        hour: { $hour: { date: '$tran.time', timezone: location_tz } },
                        minute: {
                            $subtract: [
                                { $minute: { date: '$tran.time', timezone: location_tz } },
                                { $mod: [{ $minute: '$tran.time' }, 60] },
                            ],
                        },
                    },
                    count: { $addToSet: '$tran.actor.user.uuid' },
                },
            },
            {
                $project: {
                    interv_start: { $add: ['$_id.hour', 0] },
                    interv_end: { $add: ['$_id.hour', 1] },
                    total_user_arrivals: { $size: '$count' },
                    _id: 0,
                    uuid: '$_id.uuid',
                },
            },
        ])

Can anyone help me to convert this query to opensearch. I have tried a lot but it didn't work out. Thanks

I am expecting the count of the users hour-wise like count at 12 AM, 1 AM, ...... 5 PM....11 PM, but as I mentioned any hour should not contain the count of the same user, one user can only be considered once and that too the first arrival

2

OpenSearch/OpenDistro are AWS run products and differ from the original Elasticsearch and Kibana products that Elastic builds and maintains. You may need to contact them directly for further assistance. See What is OpenSearch and the OpenSearch Dashboard? | Elastic for more details.

(This is an automated response from your friendly Elastic bot. Please report this post if you have any suggestions or concerns :elasticheart: )

3

As the bot said, we don't support something else than Elasticsearch here.

Did you look at Cloud by Elastic, also available if needed from AWS Marketplace, Azure Marketplace and Google Cloud Marketplace?

Cloud by elastic is one way to have access to all features, all managed by us. Think about what is there yet like Vector Search, Security, Monitoring, Reporting, ES|QL, Canvas, Maps UI, Alerting and built-in solutions named Observability, Security and what is coming next :slight_smile: ...