Persisting vector of strings in the database

[RibinMTC]

Hi Silvian,

So if I were to concatenate the list of vectors to a single string, how can I search for a substring in the cottontail database? I.e. which RelationalOperator could I use?

Best,

Ribin

[silvanheller]

Maybe @ppanopticon can chime in on how RelationalOperator.LIKE is supported in cottontail.
The ideal way to do Text Retrieval in Cineast (which is what we use for OCR / ASR), is to stick what we do in the AbstractTextRetriever, which is to use AttributeType.TEXT for the specific column and then use selector.getFulltextRows(k, attributename, config, terms...) which allows you to use the full spectrum of text retrieval features provided by Lucene (which is what cottontail uses for the text retrieval)

[ppanopticon]

Currently, the LIKE Operator in Cottontail DB is executed as if it was a SQL LIKE, i.e., only wildcards (% and _) are supported. Internally, these expressions are converted to RegEx or Lucene, depending on whether an Index is available.

The RelationalOperators in Cineast (LIKE, NLIKE, RLIKE etc.) are all mapped to Cottontail DB LIKE operators, since there is currently no support for other types of LIKEs in Cottontail DB.

If you want to query for a simple substring, an expression such as LIKE "%substring%" should actually do the trick. If we wanted to extend the support to, for example, full Lucene Query Parser Syntax support, we would need to add it to Cottontail DB first, which is very well possible and simply a matter of proper specification.

[RibinMTC]

Thanks, using LIKE worked to query for a substring(even without the wildcards). But what would I need to implement to achieve the negation (i.e. not like) operator?

Maybe it would help if I explain my concrete use case:
We are using a machine learning model that predicts emotions on faces in images. Since one image can have multiple faces, the model also predicts multiple emotions. Afterwards, for querying the emotion I'm currently using a boolean query with a dropdown box to select an emotion.
Is the boolean query operator even the right tool for querying multiple emotions or is there another option in cineast(or in the frontend), that is more suited for the task?

[lucaro]

You have a fixed set of possible emotions you can detect, right? So why would you store them as strings rather than categorical values?

[silvanheller]

I guess in your case it would be way easier to have one entry in the database per <segmentid, emotion> tuple, no? So the table would look something like

id emotion
1 angry
1 happy
2 happy
2 neutral
3 angry
5 happy

and then you can simply query for emotion=happy

[RibinMTC]

Yes thanks for the reply. In the end I choose the one entry per database solution.

Best,

Ribin

[silvanheller]

To summarize for future reference: There are two options:

• stick what we do in the AbstractTextRetriever, which is to use AttributeType.TEXT for the specific column and then use selector.getFulltextRows(k, attributename, config, terms...) which allows you to use the full spectrum of text retrieval features provided by Lucene (which is what cottontail uses for the text retrieval). This needs a textindex, which is automatically created when you use the entitycreator. It also means you need to optimize the entity after your import / extraction has finished.
• use LIKE operators in cottontail, which are implemented like SQL LIKEs

The solution which was best for the specific usecase was to go with a different schema, which made queries and persisting massively simpler