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

**URL:** <https://community.cratedb.com/t/multi-value-object-search-when-this-one-is-within-an-array-of-objects/1336>\
**Category:** CrateDB\
**Created:** [January 24, 2023, 10:24pm UTC](https://community.cratedb.com/t/multi-value-object-search-when-this-one-is-within-an-array-of-objects/1336 "2023-01-24T22:24:53Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Skeith00](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/skeith00/32/775_2.png) [@Skeith00](https://community.cratedb.com/u/Skeith00)\
**Post date:** [January 24, 2023, 10:24pm UTC](https://community.cratedb.com/t/multi-value-object-search-when-this-one-is-within-an-array-of-objects/1336/1 "2023-01-24T22:24:54Z")

</div>

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.

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

```

```auto
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?

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [January 25, 2023, 7:52am UTC](https://community.cratedb.com/t/multi-value-object-search-when-this-one-is-within-an-array-of-objects/1336/2 "2023-01-25T07:52:26Z")

</div>

One way that should work is

```sql
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.

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

```

---

<div class="post-metadata">

**Author:** ![Skeith00](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/skeith00/32/775_2.png) [@Skeith00](https://community.cratedb.com/u/Skeith00)\
**Post date:** [January 27, 2023, 1:05am UTC](https://community.cratedb.com/t/multi-value-object-search-when-this-one-is-within-an-array-of-objects/1336/3 "2023-01-27T01:05:05Z")

</div>

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