Filter number in range Between two numbers getting index messages error

SQL++
1

'm trying to make a query in a Couchbase Database. The idea is to retrieve the elements which are in the range of two numbers. I’m using Spring Data Couchbase.

My document looks like this:

{
“salons”: [
{
“name”: “salon_0”,
“id”: “salon-00”,
“maxAge”: 6,
“minAge”: 3
}
],
“docType”: “com.rccl.middleware.engine.repository.model.salon”
}

The query I’m trying to do is:

@Query("#{n1ql electEntity} WHERE #{n1ql ilter} AND $age BETWEEN minAge AND maxAge ")
Optional findByMinAgeAndMaxAge(@Param(“age”) int age)

But I’m getting the next issues:

Unable to execute query due to the following n1ql errors: 
{"msg":"No index available on keyspace bucketEx that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.","code":4000}

This is the query shown in the console:

SELECT META(bucketEx).id AS _ID, META(bucketEx).cas AS _CAS, bucketEx.* FROM bucketEx WHERE docType = "com.rccl.middleware.engine.repository.model.salon" AND $age BETWEEN minAge AND maxAge “,”$age":7,“scan_consistency”:“statement_plus”}

I tried to create the index, but I get the next error message:

“code”: 5000,
“msg”: “GSI CreateIndex() - cause: Fails to create index. There are not enough indexer nodes to create index with replica count of 1. Some indexer nodes may be marked as excluded.”

I added more than 100 nodes to this document, but I continue getting the same message.

There is something wrong with the query and the index creation? I must to create these indexes or there is another way to make this query?

Thanks a lot.

2 
CREATE  INDEX ix1 ON bucketEx(maxAge, minAge) 
WHERE  `docType`  = "com.rccl.middleware.engine.repository.model.salon";

Your document has salons as array may be you need to adjust query and Index.

  CREATE  INDEX ix1 ON bucketEx(DISTINCT ARRAY v.maxAge FOR v IN salons END) 
    WHERE  `docType`  = "com.rccl.middleware.engine.repository.model.salon";

SELECT META( b ).id AS _ID, META( b ).cas AS _CAS,  b.* 
FROM  `bucketEx`  AS b
 WHERE  b.`docType`  = "com.rccl.middleware.engine.repository.model.salon" AND 
                      ANY v IN b.salons SATISFIES v.minAge <= $age AND v.maxAge  >=  $age END;
1 Like
3

Yes now I am getting nodes, but for some reason it looks like it is not working:

For example with this 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;

I’m getting values that are not in the range:

[
{
“_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”
},

Any advice?

Thanks!

4

It gives right results. (“id”: “ROOM-02” qualifies this) ANY clause means any one element of array matches it returns results.
ANY v IN b.rooms SATISFIES v.minAge <= 7 AND v.maxAge >= 7 END

Please post what is your expected output for above document.

If you want remove objects from rooms that not satisfied above condition try this.

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;
5

Hi, I expect only one result for example for this case:

{
“id”: “ROOM-02”,
“maxAge”: 16,
“minAge”: 6,
“name”: “Example2”
}

It is because 7 is in this range from minAge = 6 to maxAge: 16 and for this node:

{
“id”: “ROOM-00”,
“maxAge”: 6,
“minAge”: 3,
“name”: “Nursery”
}

7 is not in this range, that’s why it must not t being shown.

Thank you!

6

Use one of the query based on your output requirement. Also change index maxAge to minAge and see which performs better based on your data and queries and use one of that.

CREATE  INDEX ix1 ON kids_club(DISTINCT ARRAY room.maxAge FOR room IN rooms END) 
    WHERE  `docType`  = "com.rccl.middleware.kidsclub.engine.repository.model.ShipRoom";

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;

OR

(UNNEST Alias must match with index  binding variable i.e. room)

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

Sorry @vsr1 with the first approach I am getting the issue:

“msg”: "No index available on keyspace kids_club that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.

The same with the second one. I’m not pretty familiar with this kind of errors. Any clue?

Thanks!

8

It should not return. Please post exact index definition and query. Make sure your docType is same in both cases.

9

This is my Index:

CREATE INDEX kids_club_r_maxAge ON kids_club(DISTINCT ARRAY v.maxAge FOR v IN rooms END)
WHERE docType = “com.rccl.middleware.kidsclub.engine.repository.model.ShipRoom”;

And Query:

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 <= 3 AND v.maxAge >= 3 END;

But I’m getting this response:

“docType”: “com.rccl.middleware.kidsclub.engine.repository.model.ShipRoom”,
“rooms”: [
{
“id”: “ROOM-02”,
“maxAge”: 16,
“minAge”: 6,
“name”: “Example2”
},
{
“id”: “ROOM-03”,
“maxAge”: 16,
“minAge”: 6,
“name”: “Example2”
}
]

As you can see 3 is not in this range.

In this case I would like to get this response:

{
“name”: “Nursery”,
“id”: “ROOM-00”,
“maxAge”: 6,
“minAge”: 3
}

Thank you!

10

You changed WHERE clause to v.minAge <= 3 AND v.maxAge >= 3
But left in projection ARRAY v FOR v IN b.rooms WHEN v.minAge <= 7 AND v.maxAge >= 7 END AS rooms
Please change both if that is what you want.

11

It worked. Thank you!