I’m trying to fetch records from an array of non-unique, secondary id’s. They are part of a composite index. For each one of these topLevelId
's, there can be any number of documents. I want to only fetch up to 2 of the most recent (as valued by a numeric timestamp) so I don’t potentially return thousands of ids. I would also like to count the total number that match the topLevelId
.
The simplest, functioning query I’ve been able to manage is this:
SELECT
m.topLevelId, ARRAY_AGG(m.uid) as uids
FROM main m
WHERE
m.table = 'activity_det'
AND m.topLevelId IN [
"ACHDR::1So2O7op0ZN9HG5c2d9a60c17fdb379808b227",
"ACHDR::2gxfecLGSxMbOp5c2d9a60c17fdb379808b227"
]
AND m.posted IS VALUED
GROUP BY m.topLevelId
This returns:
[
{
"topLevelId": "ACHDR::1So2O7op0ZN9HG5c2d9a60c17fdb379808b227",
"uids": [
"5c2d9a5bc17fdb379808b221"
]
},
{
"topLevelId": "ACHDR::2gxfecLGSxMbOp5c2d9a60c17fdb379808b227",
"uids": [
"5c2d9a5bc17fdb379808b221"
]
}
]
I’ve tried using the slice syntax to limit my uids
to 2, but when their length is less than 2, it doesn’t return any values (some kind of index out of range issue, but no error). My ideal query feels like it would look like this, but it’s not correct:
SELECT
m.topLevelId, ARRAY_AGG(m.uid)[0:2] as uids, COUNT(uids) as total
FROM main m
WHERE
m.table = 'activity_det'
AND m.topLevelId IN [
"ACHDR::1So2O7op0ZN9HG5c2d9a60c17fdb379808b227",
"ACHDR::2gxfecLGSxMbOp5c2d9a60c17fdb379808b227"
]
AND m.posted IS VALUED
GROUP BY m.topLevelId
ORDER BY m.posted DESC