CODEWITHSUNDEEP
couchbasesql++
Sreerag
Sreerag

Reputation: 1401

Couchbase N1QL query sum from sub document array

I have the following document model in my couchbase db 

{
 type:"account"
 id : "123",
 transactions: [
   { 
      type : "credit",
      value : 100
   },
   { 
      type : "debit",
      value : 10
   }
 ]
}

How do i query all the account Ids and their sum of all credits ?

Upvotes: 1

Views: 672

Answers (1)

vsr
vsr

Reputation: 7414

Using AS ARRAY functions https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/arrayfun.html

SELECT d.id, 
   ARRAY_SUM(ARRAY v.`value` FOR v IN d.transactions WHEN v.type = "credit" END) AS s  
FROM default AS d
WHERE d.type = "account";

OR

Using subquery expression https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/subqueries.html

 SELECT d.id, 
       (SELECT RAW SUM(d1.`value`) 
        FROM d.transactions AS d1
        WHERE d1.type = "credit")[0] AS s  
    FROM default AS d
    WHERE d.type = "account";

Upvotes: 4

Related Questions