Skip to main content
Solved

Transformation SQL - check presence of a key in asset metadata


Forum|alt.badge.img

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:

View original
Did this topic help you find an answer to your question?

2 replies

  • Practitioner
  • 1 reply
  • Answer
  • March 6, 2023

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:


Forum|alt.badge.img

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


Cookie Policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie Settings