Differentiate null values in Variant Data type

[vgautam]

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?

[Alberto_Umana]

Hi @vgautam,

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

1. Object Not Found: If the specified path does not exist in the JSON object.
2. 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