Efficient querying of a field in an array


#1

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?


#2
CREATE INDEX `recording` ON `bucket`(ALL ARRAY event.eventTypeId FOR event IN  tempEvents END WHERE (`type` = "recording" AND dataSource = "storage");

select distinct raw event.eventTypeId from `bucket` rec UNNEST rec.tempEvents as event where rec.type = 'recording' and rec.dataSource = 'storage';