N1QL Query help with below document structure

Hi, I have documents in Employee collection like below

{
“id”: “employeeusage::37::45::19688”,
“f1”: 37,
“f2”: 45,
“usage1”: 600,
“usage2”: 700,
“usage3”: 800,
“epochday”: 19688
}

{
“id”: “employeeusage::37::45::19689”,
“f1”: 37,
“f2”: 45,
“usage1”: 700,
“usage2”: 800,
“usage3”: 900,
“epochday”: 19689
},
{
“id”: “employeeusage::37::45::19690”,
“f1”: 37,
“f2”: 45,
“usage1”: 800,
“usage2”: 900,
“usage3”: 1000,
“epochday”: 19690
}

{
“id”: “employeeusage::38::46::19690”,
“f1”: 38,
“f2”: 46,
“usage1”: 100,
“usage2”: 200,
“usage3”: 3000,
“epochday”: 19690
}

I need to pull the record from the Employee collection in below format.
Meaning: for the given f1 and f2 , i need to pull the usage1,2,3,epochday

=======================================
[
{
“f1”: 37,
“f2”: 45,
“usages”: [
{
“usage1”: 600,
“usage2”: 700,
“usage3”: 800,
“epochday”: 19688
},
{
“usage1”: 800,
“usage2”: 900,
“usage3”: 1000,
“epochday”: 19689
},
{
“usage1”: 800,
“usage2”: 900,
“usage3”: 1000,
“epochday”: 19690
}
]
},
{
“fid1”: 38,
“fid2”: 46,
“usages”: [
{
“usage1”: 100,
“usage2”: 200,
“usage3”: 300,
“epochday”: 19690
}
]
}
]

=======================================
Can someone help me write a N1QL query for this

SELECT
e.f1,
e.f2,
ARRAY_AGG(
{
“usage1”: e.usage1,
“usage2”: e.usage2,
“usage3”: e.usage3,
“epochday”: e.epochday
}
) AS usages
FROM Employee AS e
GROUP BY e.f1, e.f2;

1 Like
SELECT e.f1, e.f2,
       ARRAY_AGG({ e.usage1, e.usage2, e.usage3, e.epochday}) AS usages
FROM Employee AS e
GROUP BY e.f1, e.f2;