MongoDb: how to search in multiple collections?

Question

I got this structure in my mongodb (2 collections: restaurant and cocktail)

restaurant {id=1001, name="Res1", coor=[12.392, 19.123], cocktail=[13, 0, 92]}

cocktail {id=13, name="Capiroska"}, {id=167, name="Capirinha"}, {id=92, name="Negroni"}, {id=0, name="Martini"}

Multiple restaurants and multiple cocktails, N:N relationship.

My goal is to find which different cocktails I can drink within a specified area.

I've already written a query that finds all restaurants near my position like this:

mongoTemplate.find(new Query(Criteria.where("address.location").withinSphere(new Circle(latitude, longitude, radius/6371))
            ), Restaurant.class);

So that I obtain a list of restaurants.

Next steps are:

1. How to obtain distinct cocktail's id (no repetitions allowed)

2. How to look into cocktail collection in order to obtain all cocktail names

TY in advance!

Answer 1

what about using aggregation.

first perform match stage where you get all restaurant then perform unwind operation on cocktail after this you can perform group by on cocktail field. At this stage you have all unique cocktail id then perform lookup stage .

Order of stage

1. match
2. project if you want its optional
3. unwind
4. group
5. lookup
6. project //because you want only name of cocktail instead of complete collection.

The code is in kotlin just convert it to java if you are using intellij as ide then it will convert it into java for you.

var match = Aggregation.match(Criteria.where("address.location").withinSphere(Circle(latitude, longitude, radius / 6371)))
        var project = Aggregation.project("cocktail")
        var unwind = Aggregation.unwind("cocktail")
        var group = Aggregation.group("cocktail")
        var lookup = Aggregation.lookup("your cocktail collection name","_id.cocktail","id","cocktailCollection")
        var project1 = Aggregation.project("cocktailCollection.name").andExclude("_id")

    var aggregation = Aggregation.newAggregation(match,project,unwind,group,lookup,project1)

    println(aggregation) // if you want to see the query

    var result = mongoTemplate.aggregate(aggregation,String::class.java)

Answer 2

This might not answer your question completely but can help

how to obtain distinct cocktails id (no repetitions allowed)

Your cocktail is in array so direct group or distinct might not work you can use $unwind.

What $unwind does is allow you to peel off a document for each element and returns that resulting document

eg: for this object

{id=1001, name="Res1", coor=[12.392, 19.123], cocktail=[13, 0, 92]}

and this query

db.temp.aggregate( [ { $unwind: "$cocktail" } ] )

will result in

{ "_id" : 1001, "name" : "Res1", coor=[12.392, 19.123],, "cocktail" : 13 }
{ "_id" : 1001, "name" : "Res1", coor=[12.392, 19.123],, "cocktail" : 0 }
{ "_id" : 1001, "name" : "Res1", coor=[12.392, 19.123],, "cocktail" : 92 }

Now once you have all individual record you can group by cocktail

db.temp.aggregate( [ { $unwind: "$cocktail" },
    {
     "$group": {
             _id: {
                     "_id": "$_id",
                     items: {$addToSet: '$cocktail'}}
            }
        }
    }
 ] );

This should answer your 1st query

For getting cocktail names you need to use lookup, group and project something like this

db.temp.aggregate([
    {
        "$unwind": "$cocktail"       
    },
    {
        "$lookup": {
            "from": "cocktail ",
            "localField": "restaurant.cocktail._id",
            "foreignField": "_id",
            "as": "cocktails"
        }
    },  
    { "$unwind": "$cocktails" },
    {
        "$group": {
            "_id": "$_id",
            "cocktail": { "$cocktail": "$cocktail" },
        }
    },
    {
        "$project": {
            "name": 1,  
            "coor" : 1,
            "cocktail.name" : 1,
        }
    }
]).pretty()

Note: This is just one approach, might not be the best way and also untested.

Answer 3

You can search data from two or more collection using join in MongoDB. Depending on your scenario,the following links might help.

https://www.mongodb.com/blog/post/joins-and-other-aggregation-enhancements-coming-in-mongodb-3-2-part-1-of-3-introduction