Get minimum and maximum value from a Array returned from a Couchbase N1Q1 query

Question

I have a query that returns a set of ordered document keys. I need to find the minimum and maximum key from this returned array.

Query to return a list of document keys :

SELECT RAW META(d).id
FROM `orders` AS d
WHERE META(d).id LIKE 'order:%'
ORDER BY META().id LIMIT 5

Output :

[
    order:1,
    order:2,
    order:3,
    order:4,
    order:5
]

I need to get the minimum from the array so I use the following query :

SELECT RAW ARRAY_MIN(
    SELECT RAW META(d).id
    FROM `orders` AS d
    WHERE META(d).id LIKE 'order:%'
    ORDER BY META().id LIMIT 5)
)

To get the max I use the ARRAY_MAX function in the above query.

As you can see I have to fire 2 queries to get the min max values.

Question : Is there are a way to get both minimum and maximum values from an array of document keys in a single query?

Answer 1

Use CTE, or LET pre 6.5.0

WITH aval AS (SELECT RAW META(d).id
              FROM `orders` AS d
              WHERE META(d).id LIKE 'order:%'
              ORDER BY META().id LIMIT 5)
SELECT ARRAY_MIN(aval) AS min, ARRAY_MAX(aval) AS max;

Also you can use the following because your array elements are sorted.

WITH aval AS (SELECT RAW META(d).id
              FROM `orders` AS d
              WHERE META(d).id LIKE 'order:%'
              ORDER BY META().id LIMIT 5)
SELECT aval[0] AS min, aval[-1] AS max;