โ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?
4 weeks ago
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
4 weeks ago
@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?
4 weeks ago
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;
4 weeks ago
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;
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโt want to miss the chance to attend and share knowledge.
If there isnโt a group near you, start one and help create a community that brings people together.
Request a New Group