topic Re: Differentiate null values in Variant Data type in Data Engineering

Differentiate null values in Variant Data type

vgautam — Fri, 03 Jan 2025 13:04:06 GMT

Hello,

Based on the documentation here, in both scenarios below try_variant_get returns a null:

If the object cannot be found
if the object cannot be cast

How does one differentiate between the two scenarios?

Re: Differentiate null values in Variant Data type

Alberto_Umana — Fri, 03 Jan 2025 13:21:33 GMT

Hi @vgautam,

In the try_variant_get function, NULL is returned in two scenarios:

Object Not Found: If the specified path does not exist in the JSON object.
Invalid Cast: If the object at the specified path cannot be cast to the target type.

To differentiate between these two scenarios, you would need to handle the cases separately in your SQL logic. Unfortunately, the try_variant_get function itself does not provide a direct way to distinguish between these two scenarios. You may need to use additional logic or checks to determine the exact cause of the NULL value

Re: Differentiate null values in Variant Data type

vgautam — Thu, 16 Jan 2025 03:58:50 GMT

@Alberto_Umana is it possible for you to elaborate on this please? What additional logic can be used to determine the exact cause? Could you please share an example?

Re: Differentiate null values in Variant Data type

Avinash_Narala — Thu, 16 Jan 2025 04:43:21 GMT

Hi @vgautam ,

You can make use of try_variant_get and variant_get to further differentiate between the two null scenarios.

Please find the below code for your reference.

%sql WITH data AS ( SELECT parse_json('{"key1": "value", "key2": 123}') AS variant_col ) SELECT -- Check if the key 'key3' exists and its type CASE WHEN try_variant_get(variant_col, '$.key3', 'int') IS NOT NULL THEN 'Valid Cast' WHEN try_variant_get(variant_col, '$.key3', 'string') IS NOT NULL THEN 'Type Mismatch' ELSE 'Key Not Found' END AS key3_existence, -- Check if the key 'key1' exists and its type CASE WHEN try_variant_get(variant_col, '$.key1', 'int') IS NOT NULL THEN 'Valid Cast' WHEN try_variant_get(variant_col, '$.key1', 'string') IS NOT NULL THEN 'Type Mismatch' ELSE 'Key Not Found' END AS key1_type_check FROM data;

Re: Differentiate null values in Variant Data type

Avinash_Narala — Thu, 16 Jan 2025 05:09:41 GMT

Hi @vgautam ,

Please ignore the above code and use below code

%sql WITH data AS ( SELECT parse_json('{"key1": "value", "key2": 123}') AS variant_col ) SELECT -- Check if the key 'key3' exists and its type CASE WHEN variant_get(variant_col, '$.key3') IS NULL THEN 'Key Not Found' WHEN try_variant_get(variant_col, '$.key3', 'string') IS NULL THEN 'Type Mismatch' ELSE 'Valid Cast' END AS key3_existence, -- Check if the key 'key1' exists and its type CASE WHEN variant_get(variant_col, '$.key1') IS NULL THEN 'Key Not Found' WHEN try_variant_get(variant_col, '$.key1', 'string') IS NULL THEN 'Type Mismatch' ELSE 'Valid Cast' END AS key1_type_check FROM data;