How to aggregate array items based on a field(s) in array?

SQL++
1

how to aggregate array items based on a field(s) in array ?
Ex: from below document, sum of values with respective “name”.
[
{
“data”: [
{
“name”: “x”,
“value”: 5000
},
{
“name”: “y”,
“value”: 5000
},
{
“name”: “x”,
“value”: 1000
},
{
“name”: “z”,
“value”: 2000
},
{
“name”: “y”,
“value”: 3000
},
{
“name”: “x”,
“value”: 4500
}
]
},
{
“data”: [
{
“name”: “c”,
“value”: 5000
},
{
“name”: “b”,
“value”: 5000
},
{
“name”: “a”,
“value”: 5000
},
{
“name”: “b”,
“value”: 5000
},
{
“name”: “c”,
“value”: 5000
},
{
“name”: “b”,
“value”: 5000
},
{
“name”: “a”,
“value”: 5000
}
]
},
{
“data”: [
{
“name”: “s”,
“value”: 5100
},
{
“name”: “t”,
“value”: 1000
},
{
“name”: “r”,
“value”: 534
},
{
“name”: “t”,
“value”: 5300
},
{
“name”: “r”,
“value”: 10
},
{
“name”: “s”,
“value”: 67
},
{
“name”: “t”,
“value”: 890
}
]
}
]

expected output …
[
{
“data”: [
{
“name”: “x”,
“value”: 10500
},
{
“name”: “y”,
“value”: 8000
},
{
“name”: “z”,
“value”: 2000
}
]
},
{
“data”: [
{
“name”: “a”,
“value”: 10000
},
{
“name”: “b”,
“value”: 8500
},
{
“name”: “c”,
“value”: 5000
}
]
},
{
“data”: [
{
“name”: “s”,
“value”: 5167
},
{
“name”: “t”,
“value”: 7190
},
{
“name”: “r”,
“value”: 544
}
]
}
]

2

You need to use UNNEST to pry the values out of the arrays, and then ARRAY_AGG() to aggregate things into arrays again after grouping.

https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/unnest.html
https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/aggregatefun.html#array_aggexpression

Give it a try, and feel free to ask for more help if things aren’t making sense.

Here’s a bit more information about NEST and UNNEST that might be useful:

3

If you need sum with in the documents. Checkout Nested Path subquery expressions https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/subqueries.html

    SELECT  (SELECT d1.name, SUM(d1.`value`) AS s   FROM d.data AS d1 WHERE ... GROUP BY d1.name ) AS data
    FROM default AS d 
    WHERE .....;

If you need across documents.

 SELECT   d1.name, SUM(d1.`value`) AS s   
FROM default AS d 
 UNNEST d.data AS d1
WHERE .....
 GROUP BY d1.name
4

thank you , it is working and need is within document.