# Fulltext index on fields in object

**URL:** <https://community.cratedb.com/t/fulltext-index-on-fields-in-object/302>\
**Category:** CrateDB\
**Created:** [November 8, 2019, 8:11am UTC](https://community.cratedb.com/t/fulltext-index-on-fields-in-object/302 "2019-11-08T08:11:48Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![heyoka](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/heyoka/32/421_2.png) [@heyoka](https://community.cratedb.com/u/heyoka)\
**Post date:** [November 8, 2019, 8:11am UTC](https://community.cratedb.com/t/fulltext-index-on-fields-in-object/302/1 "2019-11-08T08:11:48Z")

</div>

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')
      )
      
)
```

---

<div class="post-metadata">

**Author:** ![smu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/smu/32/60_2.png) [@smu](https://community.cratedb.com/u/smu)\
**Post date:** [November 11, 2019, 8:28am UTC](https://community.cratedb.com/t/fulltext-index-on-fields-in-object/302/2 "2019-11-11T08:28:52Z")

</div>

Yes this is possible.

---

<div class="post-metadata">

**Author:** ![heyoka](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/heyoka/32/421_2.png) [@heyoka](https://community.cratedb.com/u/heyoka)\
**Post date:** [November 11, 2019, 8:30am UTC](https://community.cratedb.com/t/fulltext-index-on-fields-in-object/302/3 "2019-11-11T08:30:58Z")

</div>

Ahh, ok perfect, will try …

---

<div class="post-metadata">

**Author:** ![vinayak.shukre](https://avatars.discourse-cdn.com/v4/letter/v/76d3ee/32.png) [@vinayak.shukre](https://community.cratedb.com/u/vinayak.shukre)\
**Post date:** [April 19, 2022, 12:29pm UTC](https://community.cratedb.com/t/fulltext-index-on-fields-in-object/302/4 "2022-04-19T12:29:40Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [April 19, 2022, 1:37pm UTC](https://community.cratedb.com/t/fulltext-index-on-fields-in-object/302/5 "2022-04-19T13:37:37Z")

</div>

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`

---

<div class="post-metadata">

**Author:** ![vinayak.shukre](https://avatars.discourse-cdn.com/v4/letter/v/76d3ee/32.png) [@vinayak.shukre](https://community.cratedb.com/u/vinayak.shukre)\
**Post date:** [April 22, 2022, 7:19am UTC](https://community.cratedb.com/t/fulltext-index-on-fields-in-object/302/6 "2022-04-22T07:19:15Z")

</div>

> [@proddata](#):
>
> 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`

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.

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [April 22, 2022, 7:25am UTC](https://community.cratedb.com/t/fulltext-index-on-fields-in-object/302/7 "2022-04-22T07:25:51Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![vinayak.shukre](https://avatars.discourse-cdn.com/v4/letter/v/76d3ee/32.png) [@vinayak.shukre](https://community.cratedb.com/u/vinayak.shukre)\
**Post date:** [April 22, 2022, 7:55am UTC](https://community.cratedb.com/t/fulltext-index-on-fields-in-object/302/8 "2022-04-22T07:55:03Z")

</div>

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.