Multi-value object search in CrateDB when this one is within an array of objects

Question

I'm trying to migrate our current ES to CrateDB and one of the issues I'm facing is searching for two specific values within the same object when this object is part of an array of objects.

CREATE TABLE test.artefact (
      id INTEGER,
      metadata ARRAY(OBJECT(STATIC) AS (
          key_id INTEGER,
          value TEXT
      ))
);

insert into test.artefact(id, metadata) values (
  1,
  [
  {
    "key_id" = 1,
    "value" = 'TEST1'
  },
  {
    "key_id" = 2,
    "value" = 'TEST2'
  }
]
);

So basically, I'm trying to search metadata providing key_id and value.

A select like this one finds artefact 1 as a match, even when key and value are in different objects:

select * from test.artefact where 1 = ANY(metadata['key_id']) AND 'TEST2' = ANY(metadata['value'])

I have tried other functions, like UNNEST, with no luck.

Answer 1

Copy from CrateDB Community:

One way that should work is

SELECT *
FROM test.artefact
WHERE {key_id = 1, value = 'TEST2'} = ANY(metadata)

however this is probably not the most performant way.

together with the queries on the fields it might be quick enough.

SELECT *
FROM test.artefact
WHERE
1 = ANY(metadata['key_id'])
AND 'TEST2' = ANY(metadata['value'])
AND {key_id = 1, value = 'TEST2'} = ANY(metadata)