Hello , I have some problems when create and use an Array index. I try to count the distinct colors ids are in the XXX documents, and the query is very slow when has a lot of documents.
Here my documents structure:
[
{
“xxx”: {
“aaaa”: “car”,
"colors": [
{
“class”: “1234”,
“id”: “1234”
},
{
“class”: “2222”,
“id”: “2222”
}
],
“documentType”: “CARX”
},
{
“xxx”: {
“aaaa”: “car”,
“colors”: [
{
“class”: “3333”,
“id”: “1234”
},
{
“class”: “4444”,
“id”: “4444”
}
],
“documentType”: “CARX”
}
}
]
INDEX
CREATE INDEX test2
ON APP_SPEC_DATA
(documentType, (DISTINCT ARRAY c.id FOR c IN colors END))
WHERE documentType = ‘CARX’
and the Query
SELECT distinct(c.id)
FROM APP_SPEC_DATA AS cars
USE INDEX (test2)
UNNEST cars.colors as c
WHERE cars.documentType = 'CARX
So, when execute the query with the INDEX, returns empty results, and when execute without index, returns some results
WHY? Where I have the problem?