Transformation SQL - check presence of a key in asset metadata

Hi all,

what's the best way to write a condition in the transformation query, that checks for presence of certain key in asset metadata?

Example asset metadata:

"KEY": "VALUE"

pseudocode:

select * from _cdf.assets A where ‘KEY’ in A.metadata.keys

I’ve been provided with following solutiion by Cognite support, with a note to ask here. Is there a better/other solution?

select * from _cdf.assets 
  where metadata['KEY'] is not null;

Best answer by saliha.sajid

Hi @Jerry Dabrowski,

Did you mean something like this:

SELECT *
FROM `_cdf`.`assets`
WHERE array_contains(map_keys(metadata), "key-name")

Here are some references:

Practitioner ⭐️⭐️⭐️

Hi @Jerry Dabrowski,

Did you mean something like this:

SELECT *
FROM `_cdf`.`assets`
WHERE array_contains(map_keys(metadata), "key-name")

Here are some references:

Mowi

Hi, thank you, it looks this is what I was looking for.

Sign up