Get the values between a range in Couchbase using Between is not working as expected

Question

I'm working with a query in Couchbase and I'm trying to use a BETWEEN clause, but for some reason this query is retrieving results that are not related with the range, I think there is some problem with Integer type, I'm not pretty sure of what is happening.

SELECT META( b ).id AS _ID, META( b ).cas AS _CAS,  b.* 
FROM  `kids_club`  AS b
WHERE  b.`docType`  = "com.rccl.middleware.kidsclub.engine.repository.model.ShipRoom" AND 
        ANY v IN b.rooms SATISFIES v.minAge <= 7 AND v.maxAge  >=  7 END;



[
        {
            "_CAS": 1568040819174539264,
            "_ID": "ShipRoom::ID",
            "docType": "com.rccl.middleware.kidsclub.engine.repository.model.ShipRoom",
            "rooms": [
                {
                    "id": "ROOM-00",
                    "maxAge": 6,
                    "minAge": 3,
                    "name": "Nursery"
                },
                {
                    "id": "ROOM-01",
                    "maxAge": 6,
                    "minAge": 3,
                    "name": "Nursery"
                },
                {
                    "id": "ROOM-02",
                    "maxAge": 16,
                    "minAge": 6,
                    "name": "Example2"
                },
                {
                    "id": "ROOM-03",
                    "maxAge": 16,
                    "minAge": 6,
                    "name": "Example2"
                },
                {
                    "id": "ROOM-02",
                    "maxAge": 16,
                    "minAge": 17,
                    "name": "Example2"
                }

This is my query:

SELECT META( b ).id AS _ID, META( b ).cas AS _CAS,  b.* 
FROM  `kids_club`  AS b
WHERE  b.`docType`  = "com.rccl.middleware.kidsclub.engine.repository.model.ShipRoom" AND 
        ANY v IN b.rooms SATISFIES v.minAge <= 7 AND v.maxAge  >=  7 END;

But I got for example:

{
        "id": "ROOM-02",
        "maxAge": 16,
        "minAge": 17,
        "name": "Example2"
}

Which is wrong.

Answer 1

Your query semantics says give me whole document if any array element has minAge <= 7 AND maxAge >= 7. In your case "ROOM-3" has that info.

If you want project only rooms that meet your criteria and remove all others you should reconstruct ARRAY in the projection like below.

SELECT META(b).id AS _ID, META(b).cas AS _CAS, b.*,
  ARRAY v FOR v IN b.rooms WHEN v.minAge <= 7 AND v.maxAge >= 7 END AS rooms
FROM kids_club AS b
WHERE b.docType = "com.rccl.middleware.kidsclub.engine.repository.model.ShipRoom" AND
ANY v IN b.rooms SATISFIES v.minAge <= 7 AND v.maxAge >= 7 END;