Comparing fields as strings in Parameterrized query in Java DSL

Java SDK
,
1

I’m trying to count all documents that have a lastModifiedDate before a certain date by using the .compareTo function in a parameterized query like so:

Statement query = select(x(“COUNT(*)”).as(“count”)).from(i(bucket.name()))
.where(i(“status”).eq(s(“deleted”)).and(i(“lastModifiedDate”).toString().compareTo(format.format(deletionDate).toString())).lt(0));

First I format deletionDate so it has the same format as lastModifiedDate, then I compare the two as strings. The problem is that when debugging I see that the comparison evaluates to 46, and the query doesn’t return anything, when it should return several documents from the database. This means that identifier doesn’t work and the expression is not inserting the respective value of lastModifiedDate as it goes through all the documents.

Any help would be much appreciated.

2

turns out that in order to apply the methods .toString() and compareTo in the desired way, one has to use placeholders instead of just passing in the fields as paremeters to the query. Maybe I was just too obtuse to see this in the first place, but I think it would be useful to emphasize this distinction in the docs. Here is the refactored query that has the desired result:

//Prepare Statement
Statement statement = select(x(“COUNT(*)”).as(“count”)).from(i(bucket.name()))
.where(i(“status”).eq(s(“deleted”)).and(i(“lastModifiedDate”).lt(x("$deletionDateFormatted"))));
//Prepare place-holders
JsonObject placeholderValues = JsonObject.create().put(“deletionDateFormatted”,format.format(deletionDate).toString());
N1qlQuery query = N1qlQuery.parameterized(statement, placeholderValues);
// execute query in the database
LOGGER.info(String.format(“Attempting to execute query: {%s}”, query));
final N1qlQueryResult result = bucket.query(query);
List resultList = new ArrayList();
for(N1qlQueryRow row : result) {
resultList.add(row.value().getLong(“count”));
}

	Long count = resultList.get(0);
3

Thanks for the feedback @boris.mtdv. Opened JCBC-1230 to track this!