ARRAY_SUM with condition

SQL++
1

Hi,

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.

Thanks,
Chinh

2

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
          }
    ]
}

In case of different values 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 ......