Myujth

Pretty new to mongo and haven't been able to figure out how to perform a query.

I have an accounts collection that looks like this:

 "_id" : ObjectId("1"),
 "time" : ISODate("2018-10-20T05:57:15.372Z"),
 "profileId" : "1",
 "totalUSD" : "1015.5513030613",
 "accounts" : [
 
 "_id" : ObjectId("2"),
 "accountId" : "1",
 "currency" : "USD",
 "balance" : "530.7934159683763000",
 "available" : "530.7934159683763",
 "hold" : "0.0000000000000000",
 "exchangeRateUSD" : "1"
 ,
 
 "_id" : ObjectId("5"),
 "accountId" : "4",
 "currency" : "BTC",
 "balance" : "0.0759214200000000",
 "available" : "0.07592142",
 "hold" : "0.0000000000000000",
 "exchangeRateUSD" : "6384.995"
 ,
 ],

I store only exchangeRateUSD for each currency, and not exchangeRateXXX where XXX is currency name, because there can be an arbitrary number of currencies and currency pairs. But when I query the accounts collection it will always be queried by a currency pair, eg: BTC-USD. Keeping it simple for now, I can assume the currency pair will always be XXX-USD.

When I query the accounts collection I'd like to add a 'virtual' field to each account object: exchangeRateCrypto and then on the top-level accounts document I'd like to add totalCrypto which would just be the total account value in the given crypto. Eg: USD account balance * exchangeRateCrypto + crypto account balance * exchangeRateCrypto (which would equal 1).

My current query without the exchangeRateCrypto and totalCrypto looks like:

db.accounts.aggregate([
 $unwind: '$accounts' ,
 $match: 'accounts.currency': $in: [ 'USD', 'BTC' ] ,
 
 $group: 
 _id: '$_id',
 time: $first: '$time' ,
 profileId: $first: '$profileId' ,
 accounts: $push: '$accounts' ,
 totalUSD: $sum: $multiply: [ $toDouble: '$accounts.balance' , $toDouble: '$accounts.exchangeRateUSD' ] 
 
 
]);

I'm trying to figure out how to 'reach' into the BTC row and calculate the exchangeRateCrypto by simply doing 1 / exchangeRateUSD and then projecting/returning the accounts document and subdocument as:

 "_id" : ObjectId("1"),
 "time" : ISODate("2018-10-20T05:57:15.372Z"),
 "profileId" : "1",
 "totalUSD" : "1015.5513030613",
 "totalCrypto" : "0.1590527953", // 530.7934159683763 * 0.0001566171939 + 0.07592142 * 1
 "accounts" : [
 
 "_id" : ObjectId("2"),
 "accountId" : "1",
 "currency" : "USD",
 "balance" : "530.7934159683763000",
 "available" : "530.7934159683763",
 "hold" : "0.0000000000000000",
 "exchangeRateUSD" : "1",
 "exchangeRateCrypto" : "0.0001566171939", // 1 / 6384.995
 ,
 
 "_id" : ObjectId("5"),
 "accountId" : "4",
 "currency" : "BTC",
 "balance" : "0.0759214200000000",
 "available" : "0.07592142",
 "hold" : "0.0000000000000000",
 "exchangeRateUSD" : "6384.995",
 "exchangeRateCrypto" : "1"
 ,
 ],

but haven't been able to figure out a good way of doing this.

It seems it should be pretty straightforward, but still learning Mongo.

Any tips?

Thanks!

The solution might be a bit long and probably it can be shortened however I want you to understand proposed way of thinking step by step.

var secondCurrency = "BTC";
var secondCurrencyFieldName = "exchangeRate" + secondCurrency;
var secondCurrencyFieldNameRef = "$" + secondCurrencyFieldName;
var totalFieldName = "total" + secondCurrency;

db.accounts.aggregate([
 $unwind: "$accounts" ,
 $match: "accounts.currency": $in: [ "USD", secondCurrency ] ,
 
 $group: 
 _id: "$_id",
 time: $first: "$time" ,
 profileId: $first: "$profileId" ,
 accounts: $push: "$accounts" ,
 totalUSD: $sum: $multiply: [ $toDouble: "$accounts.balance" , $toDouble: "$accounts.exchangeRateUSD" ] 
 
,

 $addFields: 
 [secondCurrencyFieldName]: 
 $filter: 
 input: "$accounts",
 as: "account",
 cond: $eq: [ "$$account.currency", secondCurrency ] 
 
 
 
,

 $addFields: 
 [secondCurrencyFieldName]: 
 $let: 
 vars: first: $arrayElemAt: [ secondCurrencyFieldNameRef, 0 ] ,
 in: $toDouble: "$$first.exchangeRateUSD" 
 
 
 
,

 $addFields: 
 accounts: 
 $map: 
 input: "$accounts",
 as: "account",
 in: 
 $mergeObjects: [
 "$$account",
 
 [secondCurrencyFieldName]: 
 $cond: [ $eq: [ "$$account.currency", secondCurrency ] , 1, $divide: [ 1, secondCurrencyFieldNameRef ] ]
 
 
 ]
 
 
 
 
,

 $addFields: 
 [totalFieldName]: 
 $reduce: 
 input: "$accounts",
 initialValue: 0,
 in: 
 $add: [
 "$$value",
 $multiply: [ $toDouble: "$$this.balance" , "$$this." + secondCurrencyFieldName ] 
 ]
 
 
 
 

]).pretty()

So we can start with $addFields which can either add new field to existing document or repace existing field. After the $group stage you have to find the USD-XXX exchange rate (using $filter and $let + $arrayElemAt in the next pipeline stage). Having this value you can use $addFields again combined with $map and $mergeObjects to add new field to nested array and that field will represent the ratio between USD and XXX currency. Then you can use $addFields again with $reduce to get the total of all accounts for XXX currency.

Output:

 "_id" : ObjectId("5beeec9fef99bb86541abf7f"),
 "time" : ISODate("2018-10-20T05:57:15.372Z"),
 "profileId" : "1",
 "accounts" : [
 
 "_id" : ObjectId("5beeec9fef99bb86541abf7d"),
 "accountId" : "1",
 "currency" : "USD",
 "balance" : "530.7934159683763000",
 "available" : "530.7934159683763",
 "hold" : "0.0000000000000000",
 "exchangeRateUSD" : "1",
 "exchangeRateBTC" : 0.00015661719390539853
 ,
 
 "_id" : ObjectId("5beeec9fef99bb86541abf7e"),
 "accountId" : "4",
 "currency" : "BTC",
 "balance" : "0.0759214200000000",
 "available" : "0.07592142",
 "hold" : "0.0000000000000000",
 "exchangeRateUSD" : "6384.995",
 "exchangeRateBTC" : 1
 
 ],
 "totalUSD" : 1015.5513030612763,
 "exchangeRateBTC" : 6384.995,
 "totalexchangeRateBTC" : 0.15905279535242806