0

What is the proper SQL syntax to search an array text in the crate database?

My example table is:

create table 
tasks(user string, entry array(object as (taskid string, eTime timestamp))).

I tried the following which give a syntax error:

select * from program where any(entry['taskid']) ~* '.*cleanup.*';
Andrew Regan
  • 5,087
  • 6
  • 37
  • 73
Geoffrey Wells
  • 3
  • 1

1 Answers1

0

The correct syntax for the ANY operator would be:

SELECT * FROM tasks WHERE '.*cleanup.*' ~* ANY(entry['taskid']);

However, PCRE are currently not supported in combination with ANY. An alternative would be the LIKE predicate, but that is not case-insensitive (and can be quite slow if it starts with a wildcard character);

So ultimately, you could ...

... either use a fulltext index on the entry['taskid'] column with a lowercase analyzer (which is probably not the best solution, because I assume taskid is a single word and you want to use it "as is" also),

... or split up the array values into separate rows so you have a schema like:

CREATE TABLE tasks (
  user string,
  entry OBJECT AS (
    taskid STRING,
    etime TIMESTAMP
  )
) ...

The you can use

SELECT * FROM tasks WHERE entry['taskid'] ~* '.*cleanup.*';
Christian
  • 279
  • 2
  • 8
  • Keep in mind that REGEX operations can be very expensive. Using an Analyzer is usually the better option. If you need the column as-is in it's original form too you can use a [named index column definition](https://crate.io/docs/reference/sql/ddl.html#defining-a-named-index-column-definition) – mfussenegger Apr 21 '16 at 11:44