Mongodb, spring, hibernate - query syntax??

[Julia S]

Hi everyone,

I'm just starting my adventure with Mongo and I have a big application running on spring and I have the possibility to use Hibernate. I need your help to translate this SQL query to Mongo query and if somebody would be so gentle to explain me the syntax of the Mongo query soI can use it by myself. (I have read a lot in the internet but i didn’t find the examples of complex query...)

My SQL query is(it was used with mybatis):

     select date_part('day', activity_date) as day, date_part('month',activity_date) as month,date_part('year',activity_date) as year, count(*) as total
        from activity_user
        where platform = #{id} // id has to be a parameter passed to the function
        group by  date_part('day',activity_date),date_part('month',activity_date),date_part('year',activity_date)
        order by year,month,day

And I need a query that does the same thing on Mongo if somebody can help me.
* I already have Mongodb up and running and i can do all the basic querys like find() and findAll().

[Tugdual Grall]

Hello Julia,

I think it is important for you to learn more about MongoDB query and aggregation features before trying to "hide" the query into a ORM/ODM like Hibernate or Spring.

The "equivalent" of the group by in MongoDB is done using the "Aggregation Pipeline" feature documented here:

http://docs.mongodb.org/manual/core/aggregation-pipeline/

This feature allows you to group, calculate, transform/project the documents.

A quick example of what you have done in SQL may look like the following aggregation commands:

db.activity_user.aggregate([

{ $match : {platform : 01} },

{ $group : { _id :  "$activity_date" , total : { $sum : 1 }} },

{ $project : { "total" : 1, "date" : "$_id" , _id : 0} }

])

or

db.activity_user.aggregate([

{ $match : {platform : 01} },

{ $group : { _id : { day : { $dayOfMonth : "$activity_date" } , month : { $month : "$activity_date" } , year : { $year : "$activity_date" }  }, total : { $sum : 1 }} },

{ $project : { "total" : 1, "date" : "$_id" , _id : 0} }

])

These queries are not exactly the same but I have written them to show you a way to start to learn Aggregation Pipeline more or less on your data.

Once you have more experience with the MongoDB aggregation framework, you can look how it is integrate with the Java API that you want to use:

- MongoDB Java Driver : http://docs.mongodb.org/ecosystem/tutorial/use-aggregation-framework-with-java-driver/

- Morphia : https://github.com/mongodb/morphia/blob/master/morphia/src/test/java/org/mongodb/morphia/aggregation/ZipCodeDataSetTest.java (Test)

- Spring Data : http://docs.spring.io/spring-data/data-mongo/docs/1.5.2.RELEASE/reference/html/mongo.core.html#mongo.aggregation

PS : I do not know if you know it but you also have free online training that could help you to learn more about MongoDB and Java Development: https://university.mongodb.com/courses

Regards

Tug

@tgrall

[Julia S]

Hi Tug,
thanx a lot for your answer!
I have already visited some of the links that you gave me and I came up with this meanwhile:


          TypedAggregation<AttivitaUtenteMongo> agg = newAggregation(UserActivityMongo.class, //
                  match(Criteria.where("platform").is(platform.getId())), //
                  project("activityDate","platform","activityType")//
                      .andExpression("year(activityDate)").as("year") //
                      .andExpression("month(activityDate)").as("month")//
                    .andExpression("dayOfMonth(activityDate)").as("day"), //
                sort(Sort.Direction.ASC, "year","month", "day"),//
                group("activityType").count().as("total")); //
                 
       
         
          System.out.println(agg.toString());

That becomes this:


{ "aggregate" : "__collection__" ,
 "pipeline" : [ { "$match" : { "platform" : 2}} , { "$project" : { "activityDate" : 1 , "platform" : 1 ,"tipoAttivita" : 1 , "year" : { "$year" : [ "$dataAttivita"]} , "month" : { "$month" : [ "$dataAttivita"]} , "day" : { "$dayOfMonth" : [ "$activityDate"]}}} , { "$sort" : { "year" : 1 , "month" : 1 , "day" : 1}} , { "$group" : { "_id" : "$activityType" , "total" : { "$sum" : 1}}}]}

But now the problem is that the last "group" operation loses the sorting order and as the output gives me only the the grouping field total end loses all the other fields like year, month, and day.
How can I solve this problem and why is it working like this?

Do I have to split it in the separated aggregations?(and use one on the result of the other?)

Thanks for your help and have a nice day.
Julia

[Asya Kamsky]

Julia,

Your group is on "$activityType" - that's not the same thing you are grouping by in SQL.

You should be $grouping on year,month,date same as your SQL (and same as Tug's second example).

Also your sort is in the wrong place - you should sort *after* grouping, and not before.

It would be {$sort:{_id:1}} since _id would be a date grouping after group stage.

Asya

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/f4f048d1-021b-4ade-8d20-0278501d6126%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.