CODEWITHSUNDEEP
mongodb
Krishna
Krishna

Reputation: 43

Mongo nested query with keys

Need help on MongoDB nested query. Below is my mongo collection.

Preference collection

{
    "_id" : "user123",
    "preferences" : {
        "product-1" : {
            "frequency" : "Weekly",
            "details" : {
                 "email" : {
                    "value" : "On"
                }
            }
        },
        "product-2" : {
            "preferencesFor" : "mpc-other",
            "preferencesForType" : "Product",
            "details" : {
                "email" : {
                    "value" : "Off"
                }
            }
        },
        "product-3" : {
            "preferencesFor" : "mpc-other",
            "preferencesForType" : "Product",
            "details" : {
                "email" : {
                    "value" : "On"
                }
            }
        }
    }   
}

Product Collection

{
    "_id" : "product-1",
     "name" : "Geo-Magazine" 
}
{
    "_id" : "product-2",
     "name" : "History-Magazine"
}
{
    "_id" : "product-3",
     "name" : "Science-Magazine"   
}

product-1, product-2... are keys from a Map. The keys are stored in another collection Product Collection. Can I create a nested query to cross-reference the product keys from another table? I need the output in the below table format. Please suggest.

user123 product-1 email On

user123 product-2 email Off

user123 product-3 email On

I tried the below but can't get result. Please suggest.

var cursor = db.productSummary.find();   
while(cursor.hasNext()){
    var sku = cursor.next()._id;
    var skuCol = "preferences."+sku+".details.email";
    var skuVal = "preferences."+sku+".details.email.value";
 db.marketingPreferences.find( {}, {_id:1, skuCol:1, skuVal:1});     
}

Upvotes: 1

Views: 531

Answers (3)

Rohit Jain
Rohit Jain

Reputation: 2092

> var myCursor = db.productSummary.find();
> while(myCursor.hasNext()){   
var sku = myCursor.next()._id;    
var skuCol = "preferences."+sku+".details.email";    
var skuVal = "$preferences."+sku+".details.email.value";    
var result = db.marketingPreferences.aggregate([{"$project":{"_id":1,value:skuVal,preferences:{$literal: sku}}}],{allowDiskUse: true});
    while(result.hasNext()){ 
        printjson(result.next());    
    }      
}

Result 

{ "_id" : "user123", "preferences" : "product-1", "value" : "On" }

{ "_id" : "user123", "preferences" : "product-2", "value" : "Off" }

{ "_id" : "user123", "preferences" : "product-3", "value" : "On" }

Upvotes: 1

Rohit Jain
Rohit Jain

Reputation: 2092

public static void test(){
        MongoCollection<Document> collection = getDatadase().getCollection("product");
        MongoCollection<Document> pref = getDatadase().getCollection("pref");

        List<Document> allDocList =  collection.find().into(new ArrayList<Document>());
        for(Document doc:allDocList){
            System.out.println(doc.get("_id"));
            String preferences = doc.get("_id")+"";
            String sku = "$preferences."+preferences+".details.email.value";

            Document aggregation = new Document().append("$project", new Document().append("_id", 1).append("value", sku));
            List<Document> pipeline = new ArrayList<Document>();
            pipeline.add(aggregation);

            List<Document> aggList = pref.aggregate(pipeline).into(new ArrayList<Document>());

            for(Document doc1:aggList){
                System.out.println(doc1.append("preferences", preferences));
            }
        }
    }

This Will return product-1

Document{{_id=user123, value=On, preferences=product-1}}

product-2

Document{{_id=user123, value=Off, preferences=product-2}}

product-3

Document{{_id=user123, value=On, preferences=product-3}}

Upvotes: 0

Suhas Shelar
Suhas Shelar

Reputation: 983

There's a difference between MongoDB and normal SQL DB. Firstly, when you query a MongoDB collection, it doesn't return a row as it will in a SQL db. What you get here is a document similar to JSON.

Also when you use preferences.product-1.details.email : 1 it wont return you the word 'email', rather it will return you the value ie. {"value" : "On" }.

Using this: db.preference.find({},{"_id":1,"preferences.product1.details.email.value":1})

you will be able to get two details which are user123 and On and you can get product-1 from your previous query. You can store these values in a variable and keep printing them to obtain the table necessary. Also you would need another cursor to store the result of the second second query that you would do.

Here's what your query will produce if it was single standalone query:

> db.preference.find({},{"_id":1,"preferences.product1.details.email.value":1}) .pretty()

{
"_id": "user123",
"preferences": {
    "product-1": {
        "details": {
            "email": {
                "value": "On"
            }
        }
    }
}
}

Upvotes: 0

Related Questions