iT邦幫忙

0

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

篩選條件為 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" }
            }
    }
])

先感謝協助

1 個回答

1
listennn08
iT邦高手 7 級 ‧ 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

vicentli iT邦新手 2 級 ‧ 2020-10-20 00:05:31 檢舉

感謝您,這也算是一種解法。
我後來想通了,order 資料先取出來,再從程式做加總

我要發表回答

立即登入回答