Composite index not used by query

SQL++
, , ,
1

Example document:

“spot”: {
“conv_owner_id”: 244,
“conv_recipients”: [
227,
6
],
“type”: “conversation”
}

Index:

CREATE INDEX idx_1 ON spots
(conv_owner_id, DISTINCT ARRAY r FOR r IN conv_recipients END)
WHERE (type = “conversation”)

Query:

SELECT *
FROM spots
WHERE type = ‘conversation’
AND (
conv_owner_id = 6
OR ANY r IN conv_recipients SATISFIES r=6 END
)

I cannot get the query to use the aforementioned index.
When one of the document elements (ie. conv_owner_id or conv_recipients) are used in index and query respectively, the index is used. But when used combined in the index and query, they are not!

2

Duw to OR it can’t use because index doesn’t maintain when leading index key is missing in the document. Every OR term must have OR index.

Option 1: Create another index

  CREATE INDEX  `idx_2`  ON  `spots`
(  DISTINCT ARRAY r FOR r IN  `conv_recipients`  END)
WHERE ( `type`  = “conversation”)

Option 2: if you are looking both same value

    CREATE INDEX idx_3 ON spots( DISTINCT ARRAY r FOR r IN ARRAY_APPEND(conv_recipients,conv_owner_id)  END)
WHERE (type = “conversation”)

SELECT *
FROM  `spots`
WHERE type = ‘conversation’
AND ANY r IN  ARRAY_APPEND(`conv_recipients`, `conv_owner_id` )  SATISFIES r=6 END
3

Good to know. Thank you very much!