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

CrateDB
1

Hello,

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 by metadata providing key_id and value but I want it to be in the same object.

A select like this one finds artefact 1 as a match, even when the 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.

Any recommendations on how to do it?

2

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)
1 Like
3

Thanks! it works perfectly. Is this object search documented? I couldn’t find it anywhere.

1 Like