MySQL query equivalent in MongoDB

Question

I am looking for help from someone who is expert on Mongodb Development. Below is My MySQL query :

select column_1,column_2,group_concat(column3 order by column4 asc ) as data_1 , group_concat(column5 order by column4 asc) as data_2 
from table_name 
where  column4>=DATE_SUB(NOW(),INTERVAL 15 MINUTE)
group by column_1,column_2

Basically I want data for past 15 minutes and group concat column data based on group by certain columns. Please help me get through this query.

Below is the sample data stored in Mongodb

{
    "_id" : ObjectId("6364fd1ae855d15c22632077"),
    "column3" : 1,
    "column_1" : 123456789,
    "column5" : "xyz",
    "source" : 1,
    "column_2" : NumberLong("22116755"),
    "column4" : ISODate("2022-11-04T11:51:55Z")
}

Please provide some sample input data.

Wernfried Domscheit
– Wernfried Domscheit

2022-11-04 12:06:01 +00:00
Commented Nov 4, 2022 at 12:06 — Wernfried Domscheit
– Wernfried Domscheit, Commented Nov 4, 2022 at 12:06
@WernfriedDomscheit edited the question with sample data.

Karnish Master
– Karnish Master

2022-11-04 12:11:46 +00:00
Commented Nov 4, 2022 at 12:11 — Karnish Master
– Karnish Master, Commented Nov 4, 2022 at 12:11

Wernfried Domscheit · Accepted Answer · 2022-11-04 12:43:04Z

1

Try this one:

db.collection.aggregate([
   { $match: { column4: { $gte: new Date(ISODate().getTime() - 1000 * 60 * 15) } } },
   { $sort: { column4: 1 } },
   {
      $group: {
         _id: { column_1: "$column_1", column_2: "$column_2" },
         data: { $push: { data_1: "$column3", data_2: "$column5" } },
      }
   },
   { $replaceWith: { $mergeObjects: ["$_id", "$data"] } }
])

answered Nov 4, 2022 at 12:43

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Karnish Master Over a year ago

its saying : 2022-11-04T19:01:42.889+0530 E QUERY [thread1] Error: command failed: { "code" : 40324, "ok" : 0, "errmsg" : "Unrecognized pipeline stage name: '$replaceWith'" } : aggregate failed : _getErrorWithCode@src/mongo/shell/utils.js:25:13 doassert@src/mongo/shell/assert.js:16:14 assert.commandWorked@src/mongo/shell/assert.js:403:5 DB.prototype._runAggregate@src/mongo/shell/db.js:260:9 DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1212:12 @(shell):1:1

Wernfried Domscheit Over a year ago

See $replaceWith: New in version 4.2

Karnish Master Over a year ago

Hi @Wernfried Domscheit Any alternatives ? We are running on Mongo version 3.6.3

Wernfried Domscheit Over a year ago

Did you read the doc? It says "The $replaceWith is an alias for $replaceRoot" - $replaceRoot was introduced long time ago! Sometimes I am really surprised how lazy people can be.

Karnish Master Over a year ago

Apologies I didnt read the doc at that point of time !

Collectives™ on Stack Overflow

MySQL query equivalent in MongoDB

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related