Native Query for Mongo

[tucker-eventpipe]

Feature description

I love Micronut data JDBC and how when I need to I can create a native query and Micronaut handles the mapping to a data object. Mongo had annotations but they are not the equivalent to a native query. You have find and aggregate but you do not have count, so If I want to do a more involved query I have to figure out how to port my query into your Criteria API, which is powerful and can be useful, but I find it a bit cumbersome at times.

For example, this is a simple enough query to write,

db.content.count(
    { "id":{$in: :ids}}, // replaced with 
    { "category.name":{$in: :categories}}
)

but to do this in Micronaut data I have to go into the Criteria API, or write an obnoxiously long method name like coundIdInListAndCategoryNameInList , which because of the nested key I'm not sure if that would even work.

Also for @MongoFindOptions() this might be my ignorance or lack of examples in the documentation, but can that reference parameter? If not having the ability to do a native query where I can just add:

   .projection({})
   .sort({_id:-1})
   .skip(20)
   .limit(100)

and be able to parameterize them would be nice rather than using a bunch of annotationss.

[dstepanov]

I'm unsure why you would need @MongoFindOptions for that case.
Have you tried @MongoFindQuery? You can combine it with Pageable in the latest version.

[tucker-eventpipe]

Yes, I have used Pageable in some cases, but not all and I still want a way to do a native query. As far as I can see pageable doesn't work with aggregate queries. I also found that I can't use the Criteria API, for my count, as I get an unsupported operation error because the structure I have, which comes from an external source, that I don't control is heavenly nested. The only option I found that works for what I'm doing is an agregate query, which is slower that what I can do with a native query.

I was trying to give some basic examples of reasons I want a native query. A lot of what is in Micronaut Data for Mongo is very useful and saves time for 98%(random percentage) of the queries I have to write, but there are the last 2%(random percentage) where it is hard to shoehorn the query into Micronaut Data, where a native query option would be quicker, easier to implement, and maintain. While there is a workaround I would like the option of a native query so I don't have to spend time figuring out a workaround for more complex queries, that I can just do in a native query. I know I could also get another library that will allow me to do a native query, but then I'll have to write the mapping myself, which is less convenient, time-consuming and more error-prone.

[tucker-eventpipe]

Also, I know that adding a native query option will take some time. I don't expect it instantly. which is why I'm putting this feature request so that in the future(hopefully not super far future) things will be easier to deal with and I might be able to clean up some code and make some queries more efficient.

[dstepanov]

We already support native queries, please check the documentation

[tucker-eventpipe]

That's where I'm going to have to disagree with you on, because I see nowhere in section 7 that details how to do a native query for Mongo. You have the following annotations, which allow you to do part of a native query, but not the whole:

@MongoFindQuery
@MongoAggregateQuery
@MongoUpdateQuery
@MongoDeleteQuery
@MongoFilter
@MongoSort
@MongoProjection
@MongoCollation
@MongoAggregateOptions
@MongoFindOptions
@MongoUpdateOptions
@MongoDeleteOptions

If I'm wrong and there is a subsection that I'm overlooking that does a whole native query, let me know what it is and I'll reread it several times. I'm dyslexic so I'm not precluding the possibility that I missed something, but I don't see it.

I want to be able to copy from my Mongo client the whole query and with little modifications for parameterization be able to run it. Currently, I have to break it down into the annotations you have(listed) above, which doesn't always work the way I can run a query from my Mongo client. I'm also missing the ability to run a count like this:

db.content.count(
    { "id":{$in: :ids}}, // replaced with 
    { "category.name":{$in: :categories}}
)

As far as I can see this is not possible with the current set of annotations, but is possible in a Mongo client.

[dstepanov]

Do you think that we should add MongoCountQuery?

[tucker-eventpipe]

It would be a nice addition, don't get me wrong, but I don't know how many other commands you don't have an annotation for and I don't know that it's worth it to add annotations for all of them. Looking through this and drilling down to the methods in each section gives you an idea:
https://www.mongodb.com/docs/manual/crud/

that's why I'm pushing for a native query option, in the future, and by that I mean the next major release, maybe months from now, because I have no idea what parts of Mongo I'm going to need in the future.

For now, with the count, the workaround I have is to do an aggregate query which is slower, but for now, in the project I'm working on no one is going to notice the difference of 10ms.

To be honest this is the first project I've worked on that I've really dove into Mongo. I know a lot more about SQL than I do Mongo. There are things about it I love and things that drive me crazy, like tool standardization. I'm not a fan of Mongo Compass, NoSQLBooster is a freeminum app, but I do like how it gets out of your way and allows you to just write queries not too dissimilar to how a lot of SQL clients work.