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?

vsr · Accepted Answer · 2021-03-06T14:31:07.957

2

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;

edited Mar 06 '21 at 14:31

answered Mar 05 '21 at 14:36

vsr

7,149
1
11
10

Worked like a charm. Thanks! – humbleCoder Mar 06 '21 at 06:49
Turns out With clause is only available in Couchbase 6.5 and we use 5.5 in prod :-(. Is there other way to do this. I want to divide the couchbase keyset into pages. To get a page I need the start and end index of each page for which I need the min max for each page. – humbleCoder Mar 11 '21 at 11:56

score 1 · Answer 2 · answered Mar 05 '21 at 14:43

Why not a query like this?

SELECT MIN(META().id) AS min, MAX(META().id) AS max
FROM orders;

You can't use RAW on this query, but you could workaround that if necessary.

But I think you're going to run into another issue any way you do it: lexicographical ordering. Couchbase document keys are strings. E.g. "order:100" comes before "order:2" in the alphabet. So you'll either need to make your keys with leading zeros (e.g. "order:00002" instead of "order:2"), or you'll need to parse the key into a number and sort by that (e.g. use of TONUMBER, REGEXP_*, etc)

Thanks for pointing out 'lexicographical ordering', it will help me in the future. However for this particular use case it doesn't matter to me how they are sorted as along as they are sorted in the same sequence consistently. — humbleCoder, Mar 06 '21 at 06:52

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

2 Answers2