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?
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
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;
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;