Couchbase full-text search and compound keys

Question

I have the following data in Couchbase:

Document 06001:

{
  "type": "box",
  "name": "lxpag",
  "number": "06001",
  "materials": [
    {
      "type": "material",
      "number": "070006",
      "name": "hosepipe"
    },
    {
      "type": "material",
      "number": "080006",
      "name": "Philips screw 4mm"
    },
}

Document 12345:

{
  "type": "material",
  "number": "12345",
  "name": "Another screw"
}

Now I want to be able to query by type and name or number: for a given query type only the documents with the respective type property shall be returned. Furthermore, a second query string specifies which kinds of materials should be searched for. If a material's id or name contains (not starts with) the search term, it shall be included. If one of the materials inside a box matches the term accordingly, the whole box shall be included.

What I have come up with is:

function (doc, meta) {
  if (doc.type === 'box' && Array.isArray(doc.materials))  {
    var queryString = "";
    for (i = 0; i < doc.materials.length; ++i) {
      var material = doc.materials[i];
      if (material.name && material.number) {
        queryString += " " + material.name + " " + material.number;
      }
    }
    emit([doc.type, queryString], doc);
  } else if (doc.type === 'material') {
    var queryString = doc.name + " " + doc.number;
    emit([doc.type, queryString], doc);
  }
}

I see that this view might not be fit for substring searches (Do I need ElasticSearch for this?). Nevertheless, when I use the following query parameters:

startKey=["box","pag"]&endKey=["box\u02ad","pag\u02ad"]

...not only do I get the box but also all other documents that are returned by the view. Thus, with these keys, nothing is filtered. On the other hand, searching by key works.

How is this possible?

Answer 1

There is no good way of doing substring search with view keys. Your options are either integrating with ElasticSearch, or using N1QL, which lets you do wildcard string matches: "SELECT * FROM bucket WHERE type = 'material' and name LIKE '%screw%'"