Two collection Join in Mongo db

Working with Data
,
1

Join two collection Customer and CurrencyPair based on
currencyPair._id.branchNumber=customer._id.branchNumber and
currencyPair._id.productName=cu.product.productName
and currencyPair._id.ccyPairCatCode=customer.product.currency
Please help me

Customer Table

{ 
    "_id" : {
        "branchNumber" : "20540", 
        "baseNumber" : 2.0
    }, 
    "baseNumber" : NumberLong(2), 
    "branchNumber" : "20540", 
    "divertToManual" : "1", 
    "isSelected" : false, 
    "priceChannel" : "3", 
    "products" : [
        {
            "volimit" : "6", 
            "currency" : "3", 
            "override" : "Y", 
            "productName" : "FX", 
            "spread" : "4"
        }
    ]
}
{ 
    "_id" : {
        "branchNumber" : "20010", 
        "baseNumber" : 600104.0
    }, 
    "baseNumber" : NumberLong(600104), 
    "branchNumber" : "20010", 
    "divertToManual" : "40", 
    "isSelected" : false, 
    "priceChannel" : "3987", 
    "product" : [
        {
            "productName" : "FX", 
            "currency" : "228", 
            "spread" : "118"
        }, 
        {
            "productName" : "FX", 
            "currency" : "228", 
            "spread" : "119"
        }, 
        {
            "productName" : "FX", 
            "currency" : "228", 
            "spread" : "110"
        }
    ]
}
{ 
    "_id" : {
        "branchNumber" : "20540", 
        "baseNumber" : 20010.0
    }, 
    "baseNumber" : 20010.0, 
    "branchNumber" : "20540", 
    "divertToManual" : "11111", 
    "isSelected" : true, 
    "priceChannel" : "3333", 
    "product" : [
        {
            "productName" : "FX", 
            "currency" : "228", 
            "spread" : "118"
        }, 
        {
            "productName" : "GX", 
            "currency" : "228", 
            "spread" : "119"
        }, 
        {
            "productName" : "FX", 
            "currency" : "228", 
            "spread" : "110"
        }
    ]
}
{ 
    "_id" : {
        "branchNumber" : "20540", 
        "baseNumber" : 30302002.0
    }, 
    "baseNumber" : 30302002.0, 
    "branchNumber" : "20540", 
    "divertToManual" : "44444", 
    "isSelected" : true, 
    "priceChannel" : "000000", 
    "product" : [
        {
            "productName" : "GX", 
            "currency" : "228", 
            "spread" : "118"
        }, 
        {
            "productName" : "GX", 
            "currency" : "228", 
            "spread" : "119"
        }, 
        {
            "productName" : "FX", 
            "currency" : "228", 
            "spread" : "110"
        }
    ]
}

CurrencyPair

{ 
    "_id" : {
        "branchNumber" : "20010", 
        "ccyPairCatCode" : "240", 
        "productName" : "FX"
    }, 
    "branchNumber" : "20010", 
    "buyOrSell" : "2", 
    "productName" : "FX", 
    "ccyPairCatCode" : "240", 
    "ccyPairCatCodeGroup" : "cat-gpaks1123", 
    "ccyPairGroups" : [
        {
            "ccyPairs" : [
                "USDCNH34", 
                "EURGBP76", 
                "EURJPY87", 
                "USDKZT"
            ], 
            "groupName" : "UNGROUPED"
        }
    ]
}
{ 
    "_id" : {
        "branchNumber" : "20010", 
        "ccyPairCatCode" : "228", 
        "productName" : "FX"
    }, 
    "branchNumber" : "20010", 
    "buyOrSell" : "2", 
    "productName" : "FX", 
    "ccyPairCatCode" : "228", 
    "ccyPairCatCodeGroup" : "cat-gpaks", 
    "ccyPairGroups" : [
        {
            "ccyPairs" : [
                "USDCNH", 
                "EURGBP", 
                "EURJPY", 
                "USDKZT"
            ], 
            "groupName" : "UNGROUPED"
        }
    ]
}
{ 
    "_id" : {
        "branchNumber" : "20010", 
        "ccyPairCatCode" : "228", 
        "productName" : "GX"
    }, 
    "branchNumber" : "20010", 
    "buyOrSell" : "2", 
    "productName" : "GX", 
    "ccyPairCatCode" : "228", 
    "ccyPairCatCodeGroup" : "cat-gpaks11", 
    "ccyPairGroups" : [
        {
            "ccyPairs" : [
                "USDCNH", 
                "EURGBP", 
                "EURJPY", 
                "USDKZT"
            ], 
            "groupName" : "UNGROUPED"
        }
    ]
}
{ 
    "_id" : {
        "branchNumber" : "205", 
        "ccyPairCatCode" : "241", 
        "productName" : "FX"
    }, 
    "branchNumber" : "205", 
    "buyOrSell" : "987", 
    "productName" : "HJ", 
    "ccyPairCatCode" : "241", 
    "ccyPairCatCodeGroup" : "cat-gpaks11", 
    "ccyPairGroups" : [
        {
            "ccyPairs" : [
                "USDCNH", 
                "EURGBP", 
                "EURJPY", 
                "USDKZT"
            ], 
            "groupName" : "UNGROUPED"
        }
    ]
}
2

Please republish your sample documents after reading Formatting code and log snippets in posts.

The way they are right now, we cannot cut-n-paste them in our system to experiment.

Also publish sample documents of the desired output.

It would also help if you published what you tried and describe how it fails or how it differs from the expected output. This way we do not start from scratch and not end up in a direction that you already know that fails.

3

Thanks @Stennie_X for the reformat.

@shailendra_jha, try the following (untested) $lookup stage.

lookup = {
  "$lookup" : {
    "from" : "customer" ,
    "let" : {
      "var_branch" : "$_id.branchNumber" ,
      "var_product" : "$_id.productName" ,
      "var_currency" : "$_id.ccyPairCatCode" 
    }
    "pipeline" :
    [
     { "$match" : { "$expr" : { "$and" :
       [
         { "$eq" : [ "$_id.branchNumber" , "$$var_branch" ] } ,
         { "$eq" : [ "$product.productName" , "$$var_product" ] } ,
         { "$eq" : [ "$product.currency" , "$$var_currency" ] }
       ]
     } } }
    ]
    "as" : "lookup_result"
  }
}
4

Hi steevej,

I ran the your piece of code but not good luck.

your piece of code below:

db = db.getSiblingDB("config");
db.getCollection('customer').aggregate([
 {
  "$lookup" : {
    "from" : "currencypair" ,
    "let" : {
      "var_branch" : "$_id.branchNumber" ,
      "var_product" : "$_id.productName" ,
      "var_currency" : "$_id.ccyPairCatCode" 
    },
    pipeline :
    [
     { "$match" : { "$expr" : { "$and" :
       [
         { "$eq" : [ "$_id.branchNumber" , "$$var_branch" ] } ,
         { "$eq" : [ "$product.productName" , "$$var_product" ] } ,
         { "$eq" : [ "$product.currency" , "$$var_currency" ] }
       ]
     } } }
    ],
    "as" : "lookup_result"
  }
}
]
)

I am looking out like that

{
    "_id" : {
        "branchNumber" : "20010",
        "baseNumber" : 2
    },
    "baseNumber" : NumberLong(2),
    "branchNumber" : "20540",
    "divertToManual" : "1",
    "isSelected" : false,
    "priceChannel" : "3",
    "products" : [ 
        {
            "volimit" : "6",
            "currency" : "228",
            "override" : "Y",
            "productName" : "FX",
            "spread" : "4",
			"lookup_result" : 
        },
		
		{
		
            "volimit" : "6",
            "currency" : "228",
            "override" : "Y",
            "productName" : "FX",
            "spread" : "4",
			"lookup_result" :  [
			"ccyPairCatCode" : "240",
    "ccyPairCatCodeGroup" : "cat-gpaks1123",
    "ccyPairGroups" : [ 
        {
            "ccyPairs" : [ 
                "USDCNH34", 
                "EURGBP76", 
                "EURJPY87", 
                "USDKZT"
            ],
            "groupName" : "UNGROUPED"
        }
		
		}
		]
    ]
    
}
5

If you aggregate starting with customer and looking up from currencypair then you have to rework the variable because the structure is not same. I assumed the other direction because your sql query had currencyPair on the left side of the equations.

By the way, you used 3 different spelling for currencypair. Is it CurrencyPair, currencyPair or currencypair? Names are case-sensitive.

Since your starting on customer you need to unwind the products array before doing the lookup.

6

This topic was automatically closed after 180 days. New replies are no longer allowed.