Cloudant - Lucene range search using numbers stored as text

Question

I have a number of documents in Cloudant, that have ID field of type string. ID can be a simple string, like "aaa", "bbb" or number stored as text, e.g. "111", "222", etc. I need to be able to full text search using the above field, but I encountered some problems.

Assuming that I have two documents, having ID="aaa" and ID="111", then searching with query:

ID:aaa
ID:"aaa"
ID:[aaa TO zzz]
ID:["aaa" TO "zzz"]

returns first document, as expected

ID:111

returns nothing, but

ID:"111"

returns second document, so at least there is a way to retrieve it.

Unfortunately, when searching for range:

ID:[111 TO 999]
ID:["111" TO "999"]

I get no results, and I have no idea what to do to get around this problem. Is there any special syntax for such case?

UPDATE:

Index function:

function(doc){
  if(!doc.ID) return;
  index("ID", doc.ID, { index:'not_analyzed_no_norms', store:true });
}

Changing index to analyzed doesn't help. Analyzer itself is keyword, but changing to standard doesn't help either.

UPDATE 2

Just to add some more context, because I think I missed one key point. The field I'm indexing will be searched using ranges, and both min and max values can be provided by user. So it is possible that one of them will be number stored as a string, while other will be a standard non-numeric text. For example search all document where ID >= "11" and ID <= "foo".

Assumig that database contains documents with ID "1", "5", "alpha", "beta", "gamma", this query should return "5", "alpha", "beta". Please note that "5" should actually be returned, because string "5" is greater than string "11".

There's no map function, as this is full text index. Question is now updated, and contains index function body. — Arek Dygas
@ArekDygas can you comment on why you would want to do this kind of range query on strings? It seems to me that if sorting and querying a range was important, you'd be using numeric values. — Raj
@Raj It's hard to explain the full purpose, without providing a detailed description of the system. In short, the field I have to query over is filled by users, that can enter anything in it, be it ABCDEF, 32342, )(*^@%, or any other value. Another user might then issue command to search over this field, providing a single value (for exact match), or two values (for a range). If I won't be able to resolve this issue, then I'll have to think on preprocessing of user entered values, but I'd like to avoid this if possible, as this might prove non-trivial. — Arek Dygas
@ArekDygas maybe your use case is more like a proximity search than a range search? Range implies finding things that lie "in between", which doesn't sound like that concept applies here. Whereas text proximity is in the sweet spot for this type of search. — Raj

Arek Dygas Arek Dygas · Accepted Answer · 2015-12-18T12:56:25

Our team just came to a workaround solution. We managed to get proper results by adding some arbitrary character, e.g. 'a' to an upper range value, and by introducing additional search term, to exclude documents having ID between upper range value and upper range value + 'a'.

When searching for a range

ID:[X TO Y]

actual query would be

(ID:[X TO Ya] AND -ID:{Y TO Ya])

For example, to find a documents having ID between 23 and 758, we execute

(ID:[23 TO 758a] AND -ID:{758 TO 758a]).

Cloudant - Lucene range search using numbers stored as text

4 Answers