Relational query

Exchange rate table data

db.forex.insert([
    {
        ccy: "USD",
        rate: 6.91,
        date: new Date("2018-12-21")
    },
    {
        ccy: "GBP",
        rate: 8.72,
        date: new Date("2018-08-21")
    },
    {
        ccy: "CNY",
        rate: 1.0,
        date: new Date("2018-10-21")
    }
])

User table data

> db.accounts.find().pretty()
{
 "_id" : ObjectId("5d80c37349f3060f1212a055"),
 "name" : {
  "first_name" : "qingquan",
  "last_name" : "zeng"
 },
 "balance" : 100,
 "currency" : [
  "CNY",
  "USD"
 ]
}
{
 "_id" : ObjectId("5d80c37349f3060f1212a056"),
 "name" : {
  "first_name" : "fengxia",
  "last_name" : "yu"
 },
 "balance" : 200,
 "currency" : "GBP"
}

Using lookup to associate queries on tables

• from table to associate
• localField current table field
• Foreign field the appearance field to be associated with the current table field
• as new field shows the result of Association

db.accounts.aggregate([
    {
        $lookup: {
            from: "forex",
            localField: 'currency',
            foreignField: "ccy",
            as: "forexData"
        }
    }
])

The results are as follows

{
 "_id" : ObjectId("5d80c37349f3060f1212a055"),
 "name" : {
  "first_name" : "qingquan",
  "last_name" : "zeng"
 },
 "balance" : 100,
 "currency" : [
  "CNY",
  "USD"
 ],
 "forexData" : [
  {
   "_id" : ObjectId("5d82226fc404a31cf5199942"),
   "ccy" : "USD",
   "rate" : 6.91,
   "date" : ISODate("2018-12-21T00:00:00Z")
  },
  {
   "_id" : ObjectId("5d82226fc404a31cf5199944"),
   "ccy" : "CNY",
   "rate" : 1,
   "date" : ISODate("2018-10-21T00:00:00Z")
  }
 ]
}
{
 "_id" : ObjectId("5d80c37349f3060f1212a056"),
 "name" : {
  "first_name" : "fengxia",
  "last_name" : "yu"
 },
 "balance" : 200,
 "currency" : "GBP",
 "forexData" : [
  {
   "_id" : ObjectId("5d82226fc404a31cf5199943"),
   "ccy" : "GBP",
   "rate" : 8.72,
   "date" : ISODate("2018-08-21T00:00:00Z")
  }
 ]
}

Relevance modification

Write foreign exchange rate of specific date to bank account document

# forexData of all accounts matches 2018-08-21 records
# After using pipeline, you can't use localField and foreign field at the same time
db.accounts.aggregate([
    {
        $lookup: {
            from: "forex",
            pipeline: [
                {
                    $match: {
                        date: new Date("2018-08-21")
                    }
                }
            ],
            as: "forexData"
        }
    }
])
{
 "_id" : ObjectId("5d80c37349f3060f1212a055"),
 "name" : {
  "first_name" : "qingquan",
  "last_name" : "zeng"
 },
 "balance" : 100,
 "currency" : [
  "CNY",
  "USD"
 ],
 "forexData" : [
  {
   "_id" : ObjectId("5d82226fc404a31cf5199943"),
   "ccy" : "GBP",
   "rate" : 8.72,
   "date" : ISODate("2018-08-21T00:00:00Z")
  }
 ]
}
{
 "_id" : ObjectId("5d80c37349f3060f1212a056"),
 "name" : {
  "first_name" : "fengxia",
  "last_name" : "yu"
 },
 "balance" : 200,
 "currency" : "GBP",
 "forexData" : [
  {
   "_id" : ObjectId("5d82226fc404a31cf5199943"),
   "ccy" : "GBP",
   "rate" : 8.72,
   "date" : ISODate("2018-08-21T00:00:00Z")
  }
 ]
}

Write foreign exchange rate of specific date to bank account document with balance greater than 100

# Use let to distinguish whether the field used by pipeline is this table field or appearance field
# To use the variables declared by let, you need to use $expr in the pipeline
db.accounts.aggregate([
    {
        $lookup: {
            from: "forex",
            let: {
                bal: "$balance"
            },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                {
                                    $eq: ["$date", new Date("2018-08-21")]
                                },
                                {
                                    $gt: ["$$bal", 100]
                                }
                            ]
                        }
                    }
                }
            ],
            as: "forexData"
        }
    }
])