Skip to main content
Solved

Transformation SQL - check presence of a key in asset metadata

J
Active
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
Andre Alves
1 person likes this
  • Andre Alves
    Andre Alves
Did this topic help you find an answer to your question?

2 replies

S
Practitioner
  • saliha.sajid
  • 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:

Sunil Krishnamoorthy
1 person likes this
  • Sunil Krishnamoorthy
    Sunil Krishnamoorthy
J
Active
Forum|alt.badge.img

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

S
1 person likes this
  • S
    saliha.sajid

Reply


Terms & ConditionsCookie Settings

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