Aggregate by object

[Ashish_M_J]

Hi ,

I’m new to the N1QL. Just want to know if there is any built in function or way to aggregate results by object .

For example :
Final result output :

[
{
id : 1
marks : {
maths : 42,
science : 45
}
},
{
id : 2
marks : {
maths : 22,
science : 15
}
}
]

[dh]

Could you could tell us what your source documents look like? We’ll be better be able to suggest a solution if we know the form of the documents you’re looking to operate on.

If you have, say, arrays of numbers you can use ARRAY_SUM; if you have arrays of objects you can use the ARRAY collection operator to extract individual fields into a simple array for use with ARRAY_SUM or you could UNNEST the array and use SUM; if you have an object with multiple differently-named fields you wish to sum as a single result you can use OBJECT_VALUES to extract the values and construct a simple array; etc.

HTH.

[Ashish_M_J]

Consider the 4 source documents -

{
id: 1
subject: maths
score : 42
}

{
id: 1
subject: science
score : 45
}

{
id: 2
subject: maths
score : 22
}

{
id: 2
subject: science
score : 15
}

[dh]

With:

CREATE INDEX ixt ON default(id);

You can use:

SELECT DISTINCT id                                                                                                                  
      ,OBJECT v.subject:v.score
       FOR v IN (SELECT subject, score
                 FROM default d2
                 WHERE d1.id = d2.id
                )
       END marks
FROM default d1
ORDER BY d1.id
;

An alternative without the correlated subquery:

SELECT id                                                                                                                           
       ,OBJECT v.subject:v.score
        FOR v IN ARRAY_AGG({"subject":subject,"score":score})
        END marks
FROM default
GROUP BY id
ORDER BY id
;

HTH.