# Convert the MongoDB query to Opensearch

**URL:** <https://discuss.elastic.co/t/convert-the-mongodb-query-to-opensearch/359402>\
**Category:** Elasticsearch\
**Tags:** aggregations\
**Created:** [May 13, 2024, 6:42pm UTC](https://discuss.elastic.co/t/convert-the-mongodb-query-to-opensearch/359402 "2024-05-13T18:42:20Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![vish\_anand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vish_anand/32/125493_2.png) [@vish\_anand](https://discuss.elastic.co/u/vish_anand)\
**Post date:** [May 13, 2024, 6:42pm UTC](https://discuss.elastic.co/t/convert-the-mongodb-query-to-opensearch/359402/1 "2024-05-13T18:42:20Z")

</div>

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

```auto
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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 13, 2024, 6:42pm UTC](https://discuss.elastic.co/t/convert-the-mongodb-query-to-opensearch/359402/2 "2024-05-13T18:42:20Z")

</div>

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](https://www.elastic.co/elasticsearch/opensearch) 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: )

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [May 13, 2024, 6:43pm UTC](https://discuss.elastic.co/t/convert-the-mongodb-query-to-opensearch/359402/3 "2024-05-13T18:43:20Z")

</div>

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

Did you look at [Cloud by Elastic](https://www.elastic.co/cloud), also available if needed from [AWS Marketplace](https://aws.amazon.com/marketplace/pp/Elasticsearch-Inc-Elasticsearch-Service-on-Elastic/B01N6YCISK), [Azure Marketplace](https://azuremarketplace.microsoft.com/en-us/marketplace/apps/elastic.ec-azure?tab=Overview) and [Google Cloud Marketplace](https://console.cloud.google.com/marketplace/details/endpoints/elasticsearch-service.gcpmarketplace.elastic.co)?

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](https://www.elastic.co/observability), [Security](https://www.elastic.co/security) and what is coming next 🙂 ...