Index Creation not getting expected result and throwing error

[mehul]

CB team,
I want to create index on “flightarea” array which is in the flightOptions object where documentId contains “TRT::” and while executing the query i should get all the documents which value has “PNR”

{
“documentId”: “TRT::Flight”,
“flightOptions”: {
“flightarea”: [
“PNR”,
“CNT”,
“TFA”
],
“resultsSet”: “100”
}

i have tried below but not getting expected result
CREATE INDEX idx_pref_custom_view ON FLIGHT_TEST (ALL ARRAY v.flightarea FOR v IN flightOptions END);

[dh]

In your attempt you don’t need to iterate over “flightOptions” since it is not an array; you can address its contents directly and need only iterate over the “flightarea” array.

Thus you could try:

CREATE INDEX ixt1 ON flight_test (ALL ARRAY a FOR a IN flightOptions.flightarea END) WHERE documentId LIKE 'TRT::%';

And the following statement should use the index:

SELECT *
FROM flight_test
WHERE documentId LIKE 'TRT::%'
AND ANY a IN flightOptions.flightarea SATISFIES a = "PNR" END
;

(You can of course confirm if you EXPLAIN the SELECT statement.)

HTH.