Aggregate field where all other params are the same

SQL++
1

Hello, I have a query that produces an output like so:

[
  {
    "firstName": "pete",
    "lastName": "smith",
   "eventDate" : "2020-01-27T18:20:50.497Z"
 },{
    "firstName": "pete",
    "lastName": "smith",
   "eventDate" : "2020-01-27T17:21:50.497Z"
 },{
    "firstName": "pete",
    "lastName": "smith",
   "eventDate" : "2020-01-27T18:11:50.497Z"
 },{
    "firstName": "mark",
    "lastName": "johnson",
   "eventDate" : "2020-01-27T18:26:50.497Z"
 },{
    "firstName": "carlos",
    "lastName": "santana",
   "eventDate" : "2020-01-27T18:21:50.497Z"
 }
]

I would like to transform that output in a way where it would consolidate the objects with the same firstName and last names and aggregate their dates into something such as an average date, (or array of diferent dates) since right now I have duplicated records I would not like to be there.
So anything like this:

[
  {
     "firstName": "pete",
     "lastName": "smith",
     "eventDate" : ["2020-01-27T18:20:50.497Z", "2020-01-27T17:21:50.497Z", "2020-01-27T18:11:50.497Z"]
   {
     "firstName": "mark",
     "lastName": "johnson",
      "eventDate" : ["2020-01-27T18:26:50.497Z"]
   },{
     "firstName": "carlos",
     "lastName": "santana",
     "eventDate" :[ "2020-01-27T18:21:50.497Z"]
   }
]

or this:

[
  {
     "firstName": "pete",
     "lastName": "smith",
     "eventDate" : "2020-01-27T18:20:50.497Z" <-where these are avgs 
   {
     "firstName": "mark",
     "lastName": "johnson",
      "eventDate" : "2020-01-27T18:26:50.497Z"<-where these are avgs 
   },{
     "firstName": "carlos",
     "lastName": "santana",
     "eventDate" : "2020-01-27T18:21:50.497Z"<-where these are avgs 
   }
]

would work for me.
Could anyone assist with this?

2 
SELECT  d.firstname, d.lastname, ARRAY_AGG(d.eventDate) AS eventDates
FROM (your query) AS d
GROUP BY d.firstname, d.lastname;

OR

SELECT  d.firstname, d.lastname, MILLIS_TO_STR(AVG( STR_TO_MILLIS(d.eventDate))) AS eventDate
FROM (your query) AS d
GROUP BY d.firstname, d.lastname;
3

Awesome, those are both the outcomes I was looking for, thanks! Any index suggestion to improve performance here?

4

Indexes are on buckets, not on subquery. Unless you give your complete query it is not possible.

Also try this https://index-advisor.couchbase.com/