[SOLVED] Querying whole OBJECT or Document using LIKE condition in CrateDB

Querying whole OBJECT or Document using LIKE condition in CrateDB

I have a table with one of the field defined as "attributes" OBJECT (DYNAMIC).

Now, my usecase is to be able to check if any particular string is part of this OBJECT. In SQL terms I want to execute a like command on this whole OBJECT or even whole Document. Do we currently support this feature

Query I want to execute: select * from gra.test_table where attributes like '%53.22.232.27%';

When I execute this on Crate 4.5.1, I am running into error: UnsupportedFeatureException[Unknown function: (gra.test_table.attributes LIKE '%53.22.232.27%'), no overload found for matching argument types: (object, text). Possible candidates: op_like(text, text):boolean]

When I execute this on Crate 3.x, I am running into error: SQLActionException[SQLParseException: Cannot cast attributes to type string]

Table structure is below and attributes is the field I am talking about

CREATE TABLE IF NOT EXISTS "test"."test_table" (
   "accountname" STRING,
   "attributes" OBJECT (DYNAMIC) AS (
      "accesslist" STRING,
      "accesslistid" STRING,
      "accessmask" STRING,
      "accessreason" STRING
   ),
   "employeeid" STRING,
   "day" TIMESTAMP,
   PRIMARY KEY ("day", "id"),
   INDEX "all_columns_ft" USING FULLTEXT ("employeeid") WITH (
      analyzer = 'standard'
   )
)
CLUSTERED INTO 1 SHARDS
PARTITIONED BY ("day")
WITH (
   "allocation.max_retries" = 5
)

Solution

Using like on a whole object is not supported. A possible (slow) workaround could be to use a regular expression operator on the text cast of an object typed column:

select * from gra.test_table where attributes::text ~ '.*53.22.232.27.*'

But be aware that through the cast, no index could be utilized and such a full table scan + filter is executed resulting in slow query execution.