Select all features from different documents having the same timestamp using GROUP BY

Hello,
I am using one bucket to store different documents having all a timestamp key.
Exemple :
I have those documents
{“timestamp”:ex,“key1”:val1,“key2”:val2}
{“timestamp”:ex,“key3”:val3,“key4”:val4}
And I want to get all the features group by the timestamp to export them in a json file
{“timestamp”:ex,“key1”:val1,“key2”:val2,“key3”:val3,“key4”:val4}
What is the Query that I need to execute please ? because when using Group By I couldn’t select all the features, do you have any idea how to do it?

Thank you so much in advance :slight_smile:

SELECT d.timestamp, (OBJECT v.name:v.val FOR v IN d.ag END).*
FROM (SELECT  d.timestamp, ARRAY_AGG(u) AS ag
                FROM default AS d
                UNNEST OBJECT_PAIRS(d) AS u
                WHERE u.name != "timestamp"
                GROUP BY d.timestamp) AS d;

Thank you so much for your reply :slight_smile:
But when executing this request I got this error
“code”: 3000,
“msg”: “syntax error - at FOR”
Do you have any Idea how to fix it?

SELECT d.timestamp, (OBJECT v.name:v.val FOR v IN d.ag END).*
FROM (SELECT  d.timestamp, ARRAY_AGG(u) AS ag
                FROM default AS d
                UNNEST OBJECT_PAIRS(d) AS u
                WHERE u.name != "timestamp"
                GROUP BY d.timestamp) AS d;
1 Like

What are the indexes that I should use for this query, please ?
Because I got this error when executing it

"code": 1080,

"msg": "Timeout 10m0s exceeded"

Thank you so much :slight_smile:

It all depends on how many documents you are talking
If you are getting timeout you should do in batches

    CREATE INDEX ix1 ON default(timestamp);

    SELECT d.timestamp, (OBJECT v.name:v.val FOR v IN d.ag END).*
    FROM (SELECT  d.timestamp, ARRAY_AGG(u) AS ag
                    FROM default AS d
                    UNNEST OBJECT_PAIRS(d) AS u
                    WHERE u.name != "timestamp" AND d.timestamp BETWEEN  xxx AND yyy 
                    GROUP BY d.timestamp) AS d;
  
  OR

    SELECT  (OBJECT v.name:v.val FOR v IN ARRAY_FLATTEN(d.ag,1) END).*
    FROM (SELECT  ARRAY_AGG(OBJECT_PAIRS(d)) AS ag
                    FROM default AS d
                    WHERE d.timestamp BETWEEN  xxx AND yyy 
                    GROUP BY d.timestamp) AS d;