mongodb 語法問題： value 為 array int 加總及只取需要的欄位語法請教

mongodb

vicentli 2020-10-19 14:58:20 ‧ 982 瀏覽

分享至

篩選條件為 id: 13c89820-11b8-11eb-be9c-55b4d0e5740f 取到的資料如下

{
    "id" : "13c89820-11b8-11eb-be9c-55b4d0e5740f",
    "exchangeRate": 1,
    "currency" : "NTD",
    "order" : [ 
        50, 
        50, 
        200, 
        0
    ],
    "totalPay" : 3700,
    "userName" : "abc",
    "userId" : "12345678",
    "time" : "2020-10-18 11:07:03",
}

用 SQL 方式說，我想要 select id, currency, 加總order, totalPay 拿到像這樣的結果

{
    "id" : "13c89820-11b8-11eb-be9c-55b4d0e5740f",
    "currency": "NTD",
    "totalOrder" : 300,   // 即為 order array 50+50+200 = 300
    "totalPay": 3700
}

有用 unwind 取到 order 的加總，但要再組語法呈現上面結果，試不出來

db.getCollection('history').aggregate([
    {
        "$match": {
            "id":"13c89820-11b8-11eb-be9c-55b4d0e5740f"
        },
    },
    {
        "$unwind": "$order",
    },
    {   
        "$group": {
            "id": "$id",
            "totalOrder" : { "$sum": "$order" }
            }
    }
])

先感謝協助

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

listennn08

iT邦高手 5 級 ‧ 2020-10-19 16:19:41

看了一下手冊 $group

Field	Description
_id	Required. If you specify an _id value of null, or any other constant value, the $group stage calculates accumulated values for all the input documents as a whole. See example of Group by Null.
field	Optional. Computed using the accumulator operators.

$group 似乎只能有一個 id

db.getCollection('history').aggregate([
  {
    "$match": {
      "id": "13c89820-11b8-11eb-be9c-55b4d0e5740f"
    },
    
  },
  {
    "$unwind": "$order",
    
  },
  {
    "$group": {
      _id: {
        id: "$id",
        currency: "$currency",
        totalPay: "$totalPay",
        
      },
      totalOrder: {
        "$sum": "$order"
      },
      
    },
  }
])

demo