# How to perform a fuzzy search on the elements of a text array

**URL:** <https://community.cratedb.com/t/how-to-perform-a-fuzzy-search-on-the-elements-of-a-text-array/2060>\
**Category:** SQL\
**Tags:** sql\
**Created:** [August 8, 2025, 3:02am UTC](https://community.cratedb.com/t/how-to-perform-a-fuzzy-search-on-the-elements-of-a-text-array/2060 "2025-08-08T03:02:13Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jun\_Zhou](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jun_zhou/32/1079_2.png) [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\
**Post date:** [August 8, 2025, 3:02am UTC](https://community.cratedb.com/t/how-to-perform-a-fuzzy-search-on-the-elements-of-a-text-array/2060/1 "2025-08-08T03:02:13Z")

</div>

Hi team, I’m using `CrateDB 5.10.11`, now I have a table to store application log in object type, and some column is ARRAY(TEXT), I want to perform fuzzy query on these column with ARRAY(TEXT). How to implement it? Thanks.

```auto
cr> select * from sdtest where log['billIds'] like '%484575b%';
UnsupportedFunctionException[Unknown function: (doc.sdtest.log['billIds'] LIKE '%484575b%'), no overload found for matching argument types: (text_array, text). Possible candidates: op_like(text, text):boolean, op_like(text, text, text):boolean]

io.crate.exceptions.UnsupportedFunctionException: Unknown function: (doc.sdtest.log['billIds'] LIKE '%484575b%'), no overload found for matching argument types: (text_array, text). Possible candidates: op_like(text, text):boolean, op_like(text, text, text):boolean
        at io.crate.metadata.Functions.raiseUnknownFunction(Functions.java:370)
        at io.crate.metadata.Functions.resolveFunctionBySignature(Functions.java:262)
        at io.crate.metadata.Functions.get(Functions.java:163)
        at io.crate.metadata.Functions.get(Functions.java:141)
        at io.crate.analyze.expressions.ExpressionAnalyzer.allocateBuiltinOrUdfFunction(ExpressionAnalyzer.java:1239)
        at io.crate.analyze.expressions.ExpressionAnalyzer.allocateFunction(ExpressionAnalyzer.java:1212)
        at io.crate.analyze.expressions.ExpressionAnalyzer.allocateFunction(ExpressionAnalyzer.java:1203)
        at io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitLikePredicate(ExpressionAnalyzer.java:875)
        at io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitLikePredicate(ExpressionAnalyzer.java:449)
        at io.crate.sql.tree.LikePredicate.accept(LikePredicate.java:61)
        at io.crate.analyze.expressions.ExpressionAnalyzer.convert(ExpressionAnalyzer.java:250)
        at io.crate.analyze.expressions.ExpressionAnalyzer.lambda$generateQuerySymbol$1(ExpressionAnalyzer.java:259)
        at java.base/java.util.Optional.map(Optional.java:260)

```

The table definition are as follows.

```auto
CREATE TABLE IF NOT EXISTS "doc"."sdtest" (
   "log" OBJECT(DYNAMIC) AS (
      "zipkinSpanId" TEXT,
      "billIds" ARRAY(TEXT),
      "optUser" TEXT,
      "auditLog" OBJECT(DYNAMIC) AS (
         "fields" OBJECT(DYNAMIC) AS (
            "isUpload" TEXT,
            "retailPrice" TEXT,
            "updTime" TEXT,
            "uploadStatusMessage" TEXT
         ),
         "afterSave" OBJECT(DYNAMIC) AS (
            "isUpload" TEXT,
            "retailPrice" TEXT,
            "updTime" TEXT,
            "uploadStatusMessage" TEXT
         ),
         "beforeSave" OBJECT(DYNAMIC) AS (
            "isUpload" TEXT,
            "retailPrice" TEXT,
            "updTime" TEXT,
            "uploadStatusMessage" TEXT
         )
      ),
      "level" TEXT,
      "typeName" TEXT,
      "ccloudTraceId" TEXT,
      "txId" TEXT,
      "zipkinTraceId" TEXT,
      "env" TEXT,
      "message" TEXT,
      "type" TEXT,
      "esIndex" TEXT,
      "prodLine" TEXT,
      "application" TEXT,
      "id" TEXT,
      "optTime" TEXT,
      "ccloudTransactionId" TEXT,
      "zipkinParentId" TEXT
   )
)
CLUSTERED INTO 8 SHARDS
WITH (
   column_policy = 'strict',
   number_of_replicas = '0-1'
)

```

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [August 8, 2025, 5:12pm UTC](https://community.cratedb.com/t/how-to-perform-a-fuzzy-search-on-the-elements-of-a-text-array/2060/2 "2025-08-08T17:12:13Z")

</div>

Hi, that error message can be a bit cryptic 😵‍💫. It means that the function that gets called with the operator `LIKE` does not haven an implementation when the input is a type `text_array`, which basically means that **LIKE cannot be used with a text array,** as far as I know it is the case with arrays in general.

`LIKE` operator would not be good for this use case as in some cases it wouldn’t be able to use the index and would have to iterate through all the rows.

For fuzzy search I’d recommend to use **fulltext-search:**

```sql
CREATE TABLE t (
  arr array(text),
  INDEX arr_ft using fulltext (arr)
)

```

```sql
INSERT INTO
  t
VALUES
  (
    [ 
        '54e68f71-d3e6-400b-b36d-d2b2a74dd29a',
        '46c87882-b521-435c-ac47-2e706d35b7a8'
     ]
)

```

```sql
SELECT
  _score,
  *
FROM
  t
WHERE
  match(arr_ft, 'd2b2a74dd29a')

```

In your case using an object you could do something like:

```sql
CREATE TABLE t (
  logs OBJECT AS (
    bills_id array(text)
  ),
  INDEX bills_id_ft using fulltext (logs ['bills_id'])
) 

```

Note that you would have to include `bills_id` in the definition of your table since adding a column that references an already existing column is not supported.

I would also encourage you to have a look at the [fulltext docs,](https://cratedb.com/docs/crate/reference/en/latest/general/dql/fulltext.html) there are options to tweak fuzziness and have different tokenizers which you might be benefit from!

---

<div class="post-metadata">

**Author:** ![Jun\_Zhou](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jun_zhou/32/1079_2.png) [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\
**Post date:** [August 11, 2025, 8:34am UTC](https://community.cratedb.com/t/how-to-perform-a-fuzzy-search-on-the-elements-of-a-text-array/2060/3 "2025-08-11T08:34:34Z")

</div>

Thanks @surister , I recreate table and specify a fulltext index as your suggestion, but the `match` function cannot return fuzzy or partial matches. Could you please help me check if something was done incorrectly?

```auto
//The original data
cr> select _score,message['billIds'] from sd.sdtest;
+--------+--------------------------------------+
| _score | message['billIds'] |
+--------+--------------------------------------+
| 1.0 | ["e2f9c9a8bc484575b42b31d87d6ec5db"] |
+--------+--------------------------------------+
SELECT 1 row in set (0.004 sec)

//the match function can return exact matches, but it cannot return fuzzy or partial matches.
cr> select _score,message['billIds'] from sd.sdtest where match(bills_id_ft,'e2f9c9a8bc484575b42b31d87d6ec5db');
+------------+--------------------------------------+
| _score | message['billIds'] |
+------------+--------------------------------------+
| 0.13076457 | ["e2f9c9a8bc484575b42b31d87d6ec5db"] |
+------------+--------------------------------------+
SELECT 1 row in set (0.018 sec)
cr> select _score,message['billIds'] from sd.sdtest where match(bills_id_ft,'e2f9c9a8bc484575b42b31d87d6e');
+--------+--------------------+
| _score | message['billIds'] |
+--------+--------------------+
+--------+--------------------+
SELECT 0 rows in set (0.004 sec)
cr>

```

The table definition

```auto
cr> show create table sd.sdtest;
+---------------------------------------------------------------------+
| SHOW CREATE TABLE sd.sdtest |
+---------------------------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "sd"."sdtest" ( |
| "message" OBJECT(DYNAMIC) AS ( |
| "zipkinSpanId" TEXT, |
| "billIds" ARRAY(TEXT), |
| "optUser" TEXT, |
| "auditLog" OBJECT(DYNAMIC) AS ( |
| "fields" OBJECT(DYNAMIC) AS ( |
| "isUpload" TEXT, |
| "retailPrice" TEXT, |
| "updTime" TEXT, |
| "uploadStatusMessage" TEXT |
| ), |
| "afterSave" OBJECT(DYNAMIC) AS ( |
| "isUpload" TEXT, |
| "retailPrice" TEXT, |
| "updTime" TEXT, |
| "uploadStatusMessage" TEXT |
| ), |
| "beforeSave" OBJECT(DYNAMIC) AS ( |
| "isUpload" TEXT, |
| "retailPrice" TEXT, |
| "updTime" TEXT, |
| "uploadStatusMessage" TEXT |
| ) |
| ), |
| "level" TEXT, |
| "typeName" TEXT, |
| "ccloudTraceId" TEXT, |
| "txId" TEXT, |
| "zipkinTraceId" TEXT, |
| "env" TEXT, |
| "message" TEXT, |
| "type" TEXT, |
| "esIndex" TEXT, |
| "prodLine" TEXT, |
| "application" TEXT, |
| "id" TEXT, |
| "optTime" TEXT, |
| "ccloudTransactionId" TEXT, |
| "zipkinParentId" TEXT |
| ), |
| INDEX "bills_id_ft" USING FULLTEXT ("message"['billIds']) WITH ( |
| analyzer = 'standard' |
| ) |
| ) |
| CLUSTERED INTO 8 SHARDS |
| WITH ( |
| column_policy = 'strict', |
| number_of_replicas = '0-1', |
| refresh_interval = 1000 |
| ) |
+---------------------------------------------------------------------+
SHOW 1 row in set (0.194 sec)
cr>

```

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [August 11, 2025, 1:02pm UTC](https://community.cratedb.com/t/how-to-perform-a-fuzzy-search-on-the-elements-of-a-text-array/2060/4 "2025-08-11T13:02:54Z")

</div>

> [@Jun\_Zhou](#):
>
> `"`

Oh yeah it makes perfect sense that it cannot match, the default analyzer is meant for common languages 🤦, so an uuid is treated as a single word, as the [docs says:](https://cratedb.com/docs/crate/reference/en/latest/general/ddl/fulltext-indices.html#indices-and-fulltext)

_Fulltext indices take the contents of one or more fields and split it up into tokens that are used for fulltext-search. The transformation from a text to separate tokens is done by an analyzer._

We could leverage [`n_gram`](https://cratedb.com/docs/crate/reference/en/latest/general/ddl/analyzers.html#n-gram-tokenizer) or [`pattern`](https://cratedb.com/docs/crate/reference/en/latest/general/ddl/analyzers.html#pattern-tokenizer) tokenizer:

```sql
CREATE ANALYZER uuid_anyzer(
  tokenizer uuid_ngram with (type = 'ngram', min_gram = 4, max_gram = 6)
)

```

This will create grams of 4-6 length.

```sql
CREATE TABLE sdtest (
  "message" OBJECT(dynamic) as ("billIds" ARRAY(TEXT)),
  INDEX "bills_id_ft" USING FULLTEXT ("message" ['billIds']) with (analyzer = 'uuid_anyzer')
)

```

```sql
INSERT INTO
  sdtest
VALUES
  (
    { "billIds" = ['93670e1a798d4670a38a54b878cd57cc'] }
  )

```

```auto
SELECT
  _score,
  message ['billIds']
FROM
  sdtest
WHERE
  match(bills_id_ft, '8cd57cc');

```

```auto
| _score | message['billIds'] |
|-----------|--------------------------------------| 
| 1.1768812 | ["93670e1a798d4670a38a54b878cd57cc"] |

```

Now, as you can see in the docs, there are a lot of analyzers, tokenizers, filters.. so _a lot_ of things can be tweaked to make your search more efficient and accurate. For example if ids are UUIDs separated by a hyphen, like `93670e1a-798d-4670-a38a-54b878cd57cc` you could use the pattern tokenizer and split tokens into indexes like: [`93670e1a, 798d, a38a, 54b878cd57cc]`if that makes sense for how you want to do search.

I will ask some teammates just in case, I’m not the most expert in fs search!

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [August 11, 2025, 1:25pm UTC](https://community.cratedb.com/t/how-to-perform-a-fuzzy-search-on-the-elements-of-a-text-array/2060/5 "2025-08-11T13:25:17Z")

</div>

While writing to the team another way of doing this occurred to me:

```sql
select * from sdtest where array_to_string(log['billIds'], '') LIKE '%85B%'

```

This might just work for you, but I’d be careful with performance, `LIKE` can in some instances do a full table scan, which might be ok or not depending on your use-case and requirements, we’d also have the overhead of calling `array_to_string` in every row iteration, you could maybe optimize for that by doing:

```sql
CREATE TABLE sdtest (
  "message" OBJECT(dynamic) as ("billIds" ARRAY(TEXT)),
  billds_ids_text generated always as array_to_string("message" ['billIds'], '')
)

```

Without question the fulltext search option will perform the best, but using `array_to_string` is way simpler, please do a good benchmark and decide what to do depending on the results and your requirements.