Index by ORDER BY ARRAY_POSITION

SQL++
1

Hi. Please help!
I have a query LIKE

SELECT OBJECT_PUT( OBJECT_REMOVE(OBJECT_REMOVE(col_doc.data.ru, 'allowed_countries'), 'restricted_countries'), 'provider', OBJECT_REMOVE(OBJECT_REMOVE(col_doc.data.ru.provider, 'allowed_countries'), 'restricted_countries') ).* ,
       col_doc.id,
       META(col_doc).cas AS ctime
FROM `cms-deploy` col_doc
LET poss=(
    SELECT RAW CASE WHEN `CA`.`Популярные}` IS NOT MISSING THEN `CA`.`Популярные}` ELSE `Популярные}` END
    FROM `cms-deploy` col_doc_pos
    WHERE col_doc_pos.id = "game_rollingslots_order" )[0]
WHERE (col_doc._type="game"
        AND col_doc._scope="rollingslots"
        AND col_doc.data.ru IS NOT MISSING
        AND ANY category IN col_doc.data.ru.categories SATISFIES (category.id IN ["6d2559be-600a-41d7-b6d0-d2204fe904bc"]
            OR categ ory.alias IN ["6d2559be-600a-41d7-b6d0-d2204fe904bc"]) END
        AND (((ARRAY_LENGTH(col_doc.data.ru.allowed_countries) + 1)=1
                AND (ARRAY_LENGTH(col_doc.data.ru.restricted_countries) + 1)=1)
            OR ((ARRAY_LENGTH(col_doc.data.ru.allowed_countri es) + 1)>1
                AND ANY i_allowed_country IN col_doc.data.ru.allowed_countries SATISFIES i_allowed_country.code="CA" END)
            OR ((ARRAY_LENGTH(col_doc.data.ru.allowed_countries) + 1)=1
                AND ANY i_restricted_country IN col_doc.data.ru.restricte d_countries SATISFIES i_restricted_country.code!="CA" END)))
ORDER BY ARRAY_POSITION(poss, col_doc.id) ASC,
         col_doc.stamp DESC

How can I build Index with ORDER BY ARRAY_POSITION(poss, col_doc.id) ?

2

Index keys/condition must be on document fields or simple expression on those. It can’t have subquery or external variables ORDER BY has external variable depends on subquery. It is not possible.

https://index-advisor.couchbase.com/indexadvisor/#1

3

But I need to sort data by data from special document and this is very slowly without indexes.
What are the ways?

4

@Lenty_Me ,

You can’t avoid sort , but you can use indexes for scan

See any way this helps

CREATE INDEX ix1 ON `cms-deploy` (id, IFMISSING(`CA`.`Популярные}`,`Популярные}`)) WHERE id = "game_rollingslots_order";
CREATE INDEX ix2 ON `cms-deploy` (_scope, DISTINCT ARRAY (DISTINCT [category.id, category.alias]) FOR category IN data.ru.categories END) WHERE type = "game";

WITH poss AS ((SELECT RAW IFMISSING(`CA`.`Популярные}`,`Популярные}`)
                FROM `cms-deploy` col_doc_pos
                WHERE col_doc_pos.id = "game_rollingslots_order" LIMIT 1)[0])
SELECT col_doc.data.ru.*,
       MISSING AS allowed_countries,
       MISSING AS restricted_countries,
       OBJECT_REMOVE(col_doc.data.ru.provider, 'allowed_countries', 'restricted_countries')  AS provider,
       col_doc.id,
       META(col_doc).cas AS ctime
FROM `cms-deploy` col_doc
WHERE col_doc._type = "game"
      AND col_doc._scope = "rollingslots"
      AND (ANY category IN col_doc.data.ru.categories
                SATISFIES (ANY v IN [category.id, category.alias]
                               SATISFIES v IN ["6d2559be-600a-41d7-b6d0-d2204fe904bc"]
                           END)
            END)
      AND (ARRAY_LENGTH(col_doc.data.ru.allowed_countries) = 0 OR "CA" IN col_doc.data.ru.allowed_countries[*].code)
      AND (ARRAY_LENGTH(col_doc.data.ru.restricted_countries) = 0 OR "CA" NOT IN col_doc.data.ru.restricted_countries[*].code)
ORDER BY ARRAY_POSITION(poss, col_doc.id) ASC, col_doc.stamp DESC;

If col_doc.id is string instead of every time doing ARRAY_POSITION() you can do map lookup

WITH poss AS ((SELECT RAW  OBJECT v:pos FOR pos:v IN IFMISSING(`CA`.`Популярные}`,`Популярные}`) END
                FROM `cms-deploy` col_doc_pos
                WHERE col_doc_pos.id = "game_rollingslots_order" LIMIT 1)[0])

..........
..........
ORDER BY poss.[col_doc.id]  ASC, col_doc.stamp DESC;

If needed

ORDER BY poss.[col_doc.id] ASC NULLS LAST, col_doc.stamp DESC;