Couchbase/N1QL: SELECT FROM list of values provided by parameter

Question

As a follow-up to Get top rows by "category" from a collection I still want to get the top5 products per categoryId, but I want to provide a pre-selected list of categoryIds that are relevant to me.

Starting with vsr's answer from the original question, I could do something like:

SELECT u.*
FROM (SELECT DISTINCT RAW p.categoryId
      FROM products AS p
      WHERE p.categoryId IN $categoryIds) AS c
UNNEST (SELECT p1.*
    FROM products AS p1
    WHERE p1.categoryId = c
    ORDER BY p1.categoryId, p1.price DESC
    LIMIT 5) AS u;

where the named parameter $categoryIds will be provided as an array ['cat1', 'cat2'].

It feels a bit inefficient to do the SELECT DISTINCT RAW p.categoryId FROM products AS p WHERE p.categoryId IN $categoryIds, just to get something back that is essentially again my list of provided categoryIds.

I am sure there is more efficient way to express this. Something like:

SELECT u.*
FROM (VALUES IN $categoryIds) AS c
UNNEST ...;

Answer 1

CREATE INDEX ix1 ON products(categoryId, price DESC);

So that below subquery in the Unnest uses index order and retrieves top 5 entries per category only irrespective of number of entries in specific category

If $categoryIds contain unique entries

SELECT u.*
FROM $categoryIds AS c
UNNEST (SELECT p1.*
       FROM products AS p1
       WHERE p1.categoryId = c
       ORDER BY p1.categoryId, p1.price DESC
       LIMIT 5) AS u;

For non-unique entries

SELECT u.*
FROM (SELECT DISTINCT RAW c1
      FROM $categoryIds AS c1 ) AS c
UNNEST (SELECT p1.*
    FROM products AS p1
    WHERE p1.categoryId = c
    ORDER BY p1.categoryId, p1.price DESC
    LIMIT 5) AS u;