CODEWITHSUNDEEP
couchbasesql++
humbleCoder
humbleCoder

Reputation: 685

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

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?

Upvotes: 1

Views: 703

Answers (2)

vsr
vsr

Reputation: 7414

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;

Upvotes: 2

Matthew Groves
Matthew Groves

Reputation: 26151

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)

Upvotes: 1

Related Questions