Sort index by first key of an array but ignore it when querying

Mobile Couchbase Lite
1

I have documents that relates between themselves in the form of a tree. I mean Document A is the root, A has 3 children (B,C,D), B is the leaf of A but the root of 2 other children (E,F), …

Here is one example (my documents has a bit more properties but it nos not relevant to this use-case):

{
	"_id": "A",
    "options": ["B", "C", "D"],
    "name": "foo"
},
{
	"_id": "B",
	"options": ["E", "F"],
	"name": "bar"
},
{
	"_id": "C",
	"name": "baz"
}

What I want is to make a full text search on the name of every first leaf of a root (B,C,D are first leafs of A but E,F are not -> they are first root of B).

So by now I have a view that emits [ROOT_KEY + word; fullName] -> here is the code:

@Override
public void map(Map < String, Object > document, Emitter emitter) {
	for (String option: ((List < String > ) document.get("options")) {
		Document optionDocument = database.getDocument(option);

		List <String> keys = new ArrayList<>();
		String name = ((String) optionDocument.getProperties().get("name")).toLowerCase();

		String[] words = name.split(" ");
		for (String word: words) {
			//Key = concatenation of root ID + child Name word (useful for start/end key).
			String key = Normalizer.normalize(((String) document.get("_id").toLowerCase() + word, NFD);
			keys.add(key);
		}

		//Add the full name key
		String fullNameKey = Normalizer.normalize(((String) document.get("_id")).toLowerCase() + name, NFD);
		keys.add(fullNameKey);

		//Emit every pair of key -> value (word -> value)
		for (String key: keys)
			emitter.emit(key, optionDocument.getProperties().get("name"));
   }
}

And a query that retrieve matching options like this (startKey = [ROOT_ID + inputText]; endKey = [startKey + endCharacter):

Query query = getDefaultDatabase().getView("searchOptions").createQuery();
String startKey = Normalizer.normalize(rootId.toLowerCase() + inputText.toLowerCase(), Normalizer.Form.NFD);
String endKey = Normalizer.normalize((startKey + "\u02AD").toLowerCase(), Normalizer.Form.NFD);

query.setStartKey(startKey);
query.setEndKey(endKey);

This is great and it’s working. Except for one thing: it does not alphabetically sort on option.name. Results are sorted by keys and keys are [ROOT_ID + word] where word is one of the word that compose option.name so the result I have is sorted by words. It means that if we have a list of options with names like this:

  • The man is on the moon
  • Your cat is beautiful
  • Thinking about philosophy

They will end it this order:

  1. Thinking about philosophy
  2. Your cat is beautiful
  3. The man is on the moon

So what I would like is that it would be sorted alphabetically on the full name (in the example above it will give 3,1,2).

Maybe I should add option.name as a first key of array so that keys will be sorted first with this? But how then would I query? Is it possible to ignore the first key of an array when querying?

2

You’ll have to do a custom sort of the returned results to get them into that order.

Searching an index always goes in order by key, so that’s the order the results appear in. And the search criteria have to match the key, so you can’t add something else at the beginning.

(FWIW, this is true of all indexes in all databases. A SQL database will internally do the same kind of in-memory sorting of results if you ask for the results in an order that doesn’t match the index.)

3

Thanks for the information !

I tought of this solution but on some cases the search result could be very large and I wanted to use pagination (I suppose I should use limit property of the query to achieve this).

But if I use pagination it won’t make any sense to sort it at the end of the query result. Results of page 2 could possibly be inserted inside page 1’s result.

I dont say this isn’t normal behavior for query result to be sorted by keys but just wondering if their isn’t any workaround to fit my use case.