ARRAY_SUM with condition

Question

I have document like this

    {
        bills: [
             { 
                  id: "1",
                  total: 20.0
              },
              { 
                  id: "1",
                  total: 20.0
              },
              { 
                  id: "2",
                  total: 10.0
              }
         ]
    }

I would like to do the DISTINCT SUM of total value with distinction based on id property but could not find and instruction for this case.

For the example case, the expected total is 30.0.

Johan Larson · Accepted Answer · 2019-04-29 16:25:14Z

2

Use the ARRAY operator to select which elements of the array you want to use.

https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/collectionops.html#array

Then use ARRAY_DISTINCT() and ARRAY_SUM() to compute the total.

https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/arrayfun.html#fn-array-distinct https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/arrayfun.html#fn-array-sum

answered Apr 29, 2019 at 16:25

Johan Larson

1,9001 gold badge11 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chinh Nguyen Over a year ago

The point is to select which elements to use, the elements are pretty much the same on the selection criteria. The ARRAY_DISTINCT works on basic data type and not object.

vsr Over a year ago

ARRAY_DISTINCT() works on any data type, In case of OBJECT if single character is different in field or value it is different. In case of ARRAY position also matters. If these functions doesn't solve use subquery expression as SELECT you can use any feature in the query

Chinh Nguyen Over a year ago

@vsr let me try the ARRAY_DISTINCT (again) on the object because it looks like better solution

Chinh Nguyen Over a year ago

the ARRAY_DISTINCT works with object (my previous try failed because I gave different total value), but the document on Couchbase site should be giving more example on this case ...

vsr · Accepted Answer · 2019-04-29 17:00:28Z

1

Which one you choose when “id”:“1” has different values

{
     bills: [
          { 
               id: "1",
              total: 20.0
          },
          { 
               id: "1",
              total: 30.0
          },
          { 
               id: "2",
              total: 10.0
          }
    ]
}

By using subquery expression https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/subqueries.html you can use complete select functionality of arrays.

In case of different values the following query uses MAX. This is per document sum

SELECT  ARRAY_SUM((SELECT RAW MAX(b.total) FROM d.bills AS b GROUP BY b.id)) AS s
FROM default AS d
WHERE ......

edited Apr 29, 2019 at 17:00

answered Apr 29, 2019 at 16:53

vsr

7,4441 gold badge13 silver badges11 bronze badges

1 Comment

Chinh Nguyen Over a year ago

This is brilliant and looks like an answer to me! I will try and let you know! To your answer the total values are the same (there are other properties which are not the same)

Collectives™ on Stack Overflow

ARRAY_SUM with condition

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related