Create Search index with a select

SQL++
, ,
1

Hi there,

I have a Json Document like:

{
  "ID" : 1,
  "Text" : "Test",
  "Times" : [
   {
       "ID" : 1,
       "Active" : true,
       "Value" : 10
   },
   {
       "ID" : 1,
       "Active" : false,
       "Value" : 20
   }
]
}

Now I want to create a Full Text Search Index, which only show the Documents with the Active Time so I can order by the Time later.
I can create a Select
SELECT ARRAY v FOR v IN Times WHEN v.Active = true end as Times FROM Recipe
WHERE any Active IN Times SATISFIES true END
Order by Times[0].Value.
But now I’m stuck. How can I create a Search index with that information?

2

Does it have to be FTS? At this moment in time there’s no direct interaction between N1QL and FTS, but you could use the tokens() function:

create index iTextActive on default(array t for t in tokens(Text) end) 
   where any v in Times satisfies v.Active=true end;

And then you would query with

select * from default
    where any t in tokens(Text) satisfies t="Test" end
         and any v in Times satisfies t.Active = true end;

That would give you very similar behaviour.

3

Don’t have to be.
I create a c# project where I want to use it as a search function like:

public IList<object> Find(string term, int position, int count)
{
  SearchQuery query = new SearchQuery();
  query.Query = new MatchQuery(term);
  query.SearchParams = new SearchParams().Skip(position).Limit(count);
  query.Index = "myIndex";
  query.Sort("Time.Value");
  
  var result = _bucket.Query(query);
}

Currently I only know the FTS as a solution.

Currently I think it would be already good enough if I can order the result by the array Value without excluding the inactive arrayelements

4 
create index ix1 on Recipe (DISTINCT ARRAY v.Active FOR v IN Times END);
SELECT ARRAY v FOR v IN Times WHEN v.Active = true END as Times 
FROM Recipe
WHERE ANY v IN Times SATISFIES v.Active = true END
Order by Times[0].Value;

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html
It only sorts first array element. If you want to sort the Array first you can use subquery expression like below

SELECT  (SELECT  RAW av FORM r.Times AS av WHERE av.Active = True ORDER BY av.Value) AS Times 
FROM Recipe AS r 
WHERE ANY v IN r.Times SATISFIES v.Active = true END
Order by Times[0].Value;
5

Just to clarify here - I thought you wanted to use FTS to index the document, ie you had some interest in indexing the context of the Text field.
Do you mean to say that you don’t care about indexing the text at all?

6

Hi @wydy

You can create a N1QL query like this:

// statement with a $1 for a positional parameter
const string statement = "select * from default where any t in tokens(Text) satisfies t=$1 end and any v in Times satisfies t.Active = true end;";

var request = new QueryRequest(statement);
query.AddPositionalParameter("test"); // set the positional parameter $1

var result = bucket.Query<dynamic>(request);
foreach(var row in result.Rows)
{

}
7

Thanks for your answers.
I’m just a beginner in Couchbase so I still have a lot to learn.

Of course I want to index the document.

I will give it a try with N1QL