Fulltext index on fields in object

CrateDB
1

Hi all,
I searched hard but could not find this information: Is it possible to set a fulltext index on subfields of an object field?
I mean like so: fulltext index on data_obj[‘message’]

CREATE TABLE fulltext_test3 (
    ts TIMESTAMP WITHOUT TIME ZONE,
    id STRING,
    topic STRING,
    data_obj OBJECT(DYNAMIC) AS (
      message TEXT INDEX using fulltext with (analyzer = 'english')
      )
      
)
2

Yes this is possible.

3

Ahh, ok perfect, will try …

4

Can full text index be added after the table is created on column message in above case? What is the syntax to add it using alter table command? Could someone please help on that?

5

Hi @vinayak.shukre

Adding indexes or generated columns is currently not possible on tables that already hold data. You would need to recreate the table and use INSERT INTO SELECT or COPY TO/FROM

6

ok Thanks. Is there any way to drop the full text index created on top of all columns? I tried alter table mytable remove all_col_ft etc but no luck.

7

could you maybe share you schema. I am not 100% sure what you are trying to achieve.

It is not possible to remove columns / indexes from an existing table as of now. This is due how CrateDB stores and indexes data with Lucene. Any removal of columns/indexes would lead to a reindex of the table (Lucene segments) or not truly delete the data, but only adjust the schema.

8

Here is my schema.

CREATE TABLE mytable (
id BIGINT,
col1 BIGINT,
col2 TEXT,
col3 BIGINT,
col4 TEXT,
col5 TEXT,
col6 TEXT,
workday TIMESTAMP WITH TIME ZONE,
moreinfo OBJECT(DYNAMIC) AS (
col10 TEXT,
col11 TEXT,
col12 TEXT,
col13 TEXT,
col14 TEXT
),
PRIMARY KEY (workday, id),
INDEX all_col_ft USING FULLTEXT (col2, col4, col5, col6, moreinfo[‘col10’], moreinfo[‘col11’])
);

I want to drop all_col_ft index as it is eating up my disk space. Need SQL command to drop it.