Getting all the related doc and aggregate data from selected doc in single n1ql query

Question

I have just started with couchbase and i have following requirement

I have following docs:

referral doc

{
    "_id": "8c658e34-34cc-4e61-8b26-5abc1cebd95e",
  "_metadata": {
    "created_at": 1472550145662,
    "created_by": "api-tenant-1"
  },
  "_type": "UWTrans",
  "amount": 4,
  "reference": {
    "refTranId": "0c0133f4-ade7-4190-8a43-a59de43846b4",
    "refTranModule": "NSP",
    "refTranType": "BUY"
  },
  "source": "WALLET",
  "tranType": "REFERRAL",
  "userId": "BJsMyG57c18f13"
} 

Now i need to have all the referral doc for specific user id and i also want sum of all the referral amount like this

[
  {
     "totalAmountEarned": 16,
     "transactions": [{
         "_id": "8c658e34-34cc-4e61-8b26-5abc1cebd95e",
         "_metadata": {
             "created_at": 1472550145662,
             "created_by": "api-tenant-1"
         },
         "_type": "UWTrans",
         "amount": 4,
         "reference": {
             "refTranId": "0c0133f4-ade7-4190-8a43-a59de43846b4",
             "refTranModule": "NSP",
            "refTranType": "BUY"
         },
        "source": "WALLET",
        "tranType": "REFERRAL",
        "userId": "BJsMyG1s57c18f13"
     },{
         "_id": "8c658e34-34cc-4e61-8b26-5abc1cebd95e",
         "_metadata": {
             "created_at": 1472550145662,
             "created_by": "api-tenant-1"
         },
         "_type": "UWTrans",
         "amount": 12,
         "reference": {
             "refTranId": "r58133f4-fd8-41h0-82t3-a59b783er6b4",
             "refTranModule": "URP",
             "refTranType": "SOLD"
         },
        "source": "WALLET",
        "tranType": "REFERRAL",
        "userId": "BJsMyG1s57c18f13"
     }]
  }
]

i want output something like this or similar . I have tried with nest but didn't get desired output

Thank you in advance

Answer 1

try this query

  SELECT sum(amount) AS totalAmountEarned, array_agg(d) AS transactions
    FROM default d WHERE _type = "UWTrans" AND userId = "BJsMyG57c18f13";
    [
      {
        "totalAmountEarned": 16,
        "transactions": [
          {
            "_id": "8c658e34-34cc-4e61-8b26-5abc1cebd95e",
            "_metadata": {
              "created_at": 1472550145662,
              "created_by": "api-tenant-1"
            },
            "_type": "UWTrans",
            "amount": 4,
            "reference": {
              "refTranId": "0c0133f4-ade7-4190-8a43-a59de43846b4",
              "refTranModule": "NSP",
              "refTranType": "BUY"
            },
            "source": "WALLET",
            "tranType": "REFERRAL",
            "userId": "BJsMyG57c18f13"
          },
          {
            "_id": "8c658e34-34cc-4e61-8b26-5abc1cebd95e",
            "_metadata": {
              "created_at": 1472550145662,
              "created_by": "api-tenant-1"
            },
            "_type": "UWTrans",
            "amount": 12,
            "reference": {
              "refTranId": "0c0133f4-ade7-4190-8a43-a59de43846b4",
              "refTranModule": "NSP",
              "refTranType": "BUY"
            },
            "source": "WALLET",
            "tranType": "REFERRAL",
            "userId": "BJsMyG57c18f13"
          }
        ]
      }
    ]