Customizing an index, Create index on dev design doc, and Customized sorting

SQL++
1

Few questions in one post I hope I will be clear :slight_smile:

  • Customizing an index: after creating an index is it healthy to customize it by editing the created view? For example I have created an index using CREATE INDEX then went to the view created in the couchbase and edited it a bit and save. Is that OK?
  • In continue to previous question, can I create an index on dev design doc and play with it(assuming that the answer for the previous question is YES)?
  • Lets say I have a text field (alphabetic) and I want to sort by it, but the sort should be by an order I decide in runtime for example I have a color field with RED, GREEN, YELLOW as values an I want to sort then with 1 as YELLOW, 2 as GREEN and 3 as RED. Today I am doing this in the views using javascript by creating a map value in the view and emit the numeric value and sort by it. Can I do it in n1ql? assuming the unswer for the first is yes I will able to :)

    • Thanks

1 Like
2

It is of course possible to customise this index and there really is nothing stopping you, but I would not recommend it. As N1QL is in preview right now, views are the way indexes are implemented, if this stays exposed this way is not clear, yet. So it is of course nice to play with but don’t count on it staying this way.

On the 2. part of the question, like you said, since number one is possible this is of course doable right now but don’t count on it. Any reason why not to use a 2. property for the ordering, and then just use ORDER BY prop DESC or alike? It duplicates data of course but might be worth trying.

1 Like
3

Thanks for the response,
Right now solved the issue with CASE WHEN like:
CASE WHEN RED then 1 WHEN BLUE then 2 WHEN YELLOW then 3 … and it works still I think that a solution in index will be more performant and clear

4

Agree with pfehre - N1QL views must not be modified. It actually enforces this by adding a checksum to the views and stops using views where the view definition has been modified.

So in general, the views generated by N1QL must be considered an implementation/machine artifact and not intended to be read or modified by the developer.

It is a very interesting problem to allow developers to define views, and have intelligence in N1QL to introspect and utilize them, perhaps with some assistance from the developer. This is one of the features we’re considering but don’t have a concrete plan at the moment for it.