How to use the LENGTH() String Function in a N1QL Query (Couchbase Query)-CodePudding

I am trying to search for an ID that is exactly 4 digits, I know I need to use the LENGTH() String Function but can't find an example of how to use it. I'm trying the below (and other variations) but they aren't working.

SELECT max(car_id) as max_id 
FROM `myBucket` 
WHERE type = 'car' 
AND car_id LIKE '28%'
AND LENGTH(max(car_id)) = 4

In case this matters, what I'm trying to do is find the largest car_id that is only 4 digits that starts with a given prefix. So 2899 will work but 28990 will not. This is for a Couchbase database using a N1QL query.

CodePudding user response：

MAX is aggregate. Aggregates can't be used in predicate without intervening subquery

SELECT max(car_id) as max_id 
FROM `myBucket` 
WHERE type = 'car' 
AND car_id LIKE '28%'
AND LENGTH(car_id) = 4;