โ01-03-2025 05:04 AM
Hello,
Based on the documentation here, in both scenarios below try_variant_get returns a null:
How does one differentiate between the two scenarios?
โ01-15-2025 09:09 PM
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;
โ01-03-2025 05:21 AM
Hi @vgautam,
In the try_variant_get
function, NULL is returned in two scenarios:
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
โ01-15-2025 07:58 PM
@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?
โ01-15-2025 08:43 PM
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;
โ01-15-2025 09:09 PM
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;
Passionate about hosting events and connecting people? Help us grow a vibrant local communityโsign up today to get started!
Sign Up Now