I’m trying to retrieve all the variations of eventTypeId across multiple documents
The documents looks a bit like this (but hundereds of tempEvents, with maybe 20-30 total variations of eventTypeId):
{
type: "recording",
dataSource: "storage",
tempEvents: [
{
eventTypeId : "IPA"
},
{
eventTypeId : "Lager"
},
]
}
and
{
type: "recording",
dataSource: "storage",
tempEvents: [
{
eventTypeId : "Pale Ale"
},
]
}
And I’m trying to return the variations of eventTypeId, so the result looks like this:
[
"IPA",
"Lager",
"Pale Ale"
]
My query looks like this:
select distinct raw event.eventTypeId from `bucket` rec UNNEST rec.tempEvents as event where rec.type = 'recording' and rec.dataSource = 'storage'
Which works, but is incredibly slow even for a small number of documents (~1000). I have an adaptive index on recording:
CREATE INDEX `recording` ON `bucket`((distinct (pairs(self)))) WHERE (`type` = "recording")
But it isn’t being used in the query.
What would be the best way to do this? Should I make a new index or change my query?