Haw to execute aggregation native query in java

Hi all, I have a native query and I want to execute it in java. I know about Java Driver’s MongoDatabase#runCommand but I don’t know how I can convert the query to correcponding Bson object from below query in java or using spring, please?

db.c2.aggregate([
    {
        '$lookup': {
            'from': 'organization',
            let: {
                c2_orgId: '$organizationId', c2_conId: '$conId', c2_enabled: '$enabled',
                c2_selected: '$selected'
            },
            pipeline: [
                {
                    $match:
                    {
                        $expr:
                        {
                            $and:
                                [
                                    { $eq: ['$_id', '$$c2_orgId'] },
                                    { $eq: ['$$c2_selected', true] },
                                    { $ne: ['$$c2_enabled', true] },
                                    { $eq: ['$$c2_name', 'NAME'] },
                                    { $ne: ['$disabled', true] }
                                ]
                        }
                    }
                }], 'as': 'res'
        }
    },
    { '$unwind': '$res' },
    {
        '$project': {
           'org.type':1;  'enabled': 1,  'org._id': 1, 'description': 1, 'created': 1, '_id': 1
        }
    }
])

The easiest way is to load the pipeline into Compass and then Export to JAVA.

I usually keep my aggregations in a resource file that I read at run time and use Document.parse(). This way, I can modify the queries without compiling. And I can use the same file in mongosh. I use special values for variable parts of the query to substitute with current value.

1 Like

Thank you bro, you saved my life!

2 Likes

How do you execute your aggregations?
mongotmpl.aggregate(Document.parse('$match...'), Document.parse('$lookup'...)) or
mongotmpl.<method>(Document.parse('{$match...},{$lookup...'))?
If the second case then which method do you use ?

You do not parse each an every stage. You parse the whole query. Something like:

query_string = """
{ pipeline : [
    {
        '$lookup': {
            'from': 'organization',
            let: {
                c2_orgId: '$organizationId', c2_conId: '$conId', c2_enabled: '$enabled',
                c2_selected: '$selected'
            },
            pipeline: [
                {
                    $match:
                    {
                        $expr:
                        {
                            $and:
                                [
                                    { $eq: ['$_id', '$$c2_orgId'] },
                                    { $eq: ['$$c2_selected', true] },
                                    { $ne: ['$$c2_enabled', true] },
                                    { $eq: ['$$c2_name', 'NAME'] },
                                    { $ne: ['$disabled', true] }
                                ]
                        }
                    }
                }], 'as': 'res'
        }
    },
    { '$unwind': '$res' },
    {
        '$project': {
           'org.type':1;  'enabled': 1,  'org._id': 1, 'description': 1, 'created': 1, '_id': 1
        }
    }
] }
""" ;
Document query = Document.parse( query_string ) ;

But how do you execute this BSon?
mongoTemplate.aggregate(query) ?

Like any other query. Document.parse gives you a Document. And according to documentation

Class Document

    java.lang.Object
        org.bson.Document 

    All Implemented Interfaces:
        Serializable, Map<String,​Object>, Bson 

Didn’t get you still, sorry.
In my application for connection with DB I use Spring’s MongoTemplate.
So, e.g. my code is loocks like:

@Autowired
private MongoTemplate mongoTemplate;
void runQuery() {
    Document query = Document.parse( query_string ) ;
  MongoDatabase db = mongoTemplate.getDb();
  db.**method**(query);
}

The question is what a method should I use to run this query?

What method do you use usually?

You should use the same one.

In more details:

I avoid abstract layers like String’s MongoTemplate so I have no idea of what method could be in this context. I just know that usually a query is performed on a collection, so method should be method from MongoDatabase that gives you a MongoCollection like getCollection(). Once you get the MongoCollection object you usually use aggregate() to run an aggregation.

But there is no magic. Calling Document.parse() is just a way to build the query. You use the query as you used any other query.

If you are not familiar enough with java and mongo together, university.mongodb.com offers a free (as in no fee) course for that.

1 Like

you can say there are two types of methods: cursor and aggregation.

the cursor is actually a category name for “find/sort/limit/skip” methods. you can append them to one other: db.find().sort().skip().limit(). “find” is the first in the line to return a cursor.

aggregation is a “pipeline”. each method above will be a single stage in the pipeline array: db.aggregate([ {$match:"..."},{$sort:"..."},{"$skip:"..."},{$limit:"..."}]). every stage can be “parsed” separately from different strings as long as they are combined in an array (array or list, which one java driver uses? check documentation). aggregation does not return a cursor unless set explicitly.

which one your query fits into?

I use also MongoDatabase object for query execution, it written in my code example.
The problem is when I break the initial query to pipeline parts and then execute db.aggregate(part1, part2…), this returns me correct result, but if I use your approach(wrap whole my aggregation chain into one pipeline) then db.find(query) return me an empty response, and db.aggregate(List.of(query)) fail with following error:

com.mongodb.MongoCommandException: Command failed with error 40324 (Location40324): 'Unrecognized pipeline stage name: 'pipeline'' on server localhost:27017. The full response is { "ok" : 0.0, "errmsg" : "Unrecognized pipeline stage name: 'pipeline'", "code" : 40324, "codeName" : "Location40324" }

If you would read the conversation a bit more thoroughly then you could know that a talking is about aggregation query wrapped up into one Bson object. How do you think, which method should be used for such case? My tries I wrote in answer above.

Now I see. The thing is that Document.parse needs a document and a pipeline is an array. That is why the query_string is an object with the field pipeline. You need to call getList() to get the array of stages.

1 Like

Thanks bro, now works like a charm!

1 Like

I already did that before answering.

but did you notice you asked many other questions after you already accepted a solution?

I do not know your understanding level of MongoDB, and thus I gave an answer to one of those questions (was in quotes): given that you have a query to parse, which method would you use to execute it?

I thought it was clear: if you take it to your original question, the method is “aggregate”. If you want to break it up into pieces it is “cursors”.

Do not forget that there is no single way to query MongoDB. There is only a first method you see working and get familiar with, and many others you will hesitate to try because we, as human beings, fear the unfamiliar.

This post was flagged by the community and is temporarily hidden.

This post was flagged by the community and is temporarily hidden.

This post was flagged by the community and is temporarily hidden.