Hello,
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
)