Variant datatype

KSI
New Contributor II

‎06-25-2024 09:46 AM

I'm checking on variant datatype and noted that whenever a JSON string is stored as a variant datatype in order to filter and value it needs to be casted: i.e
SELECT sum(jsondatavar:Value::double )
FROM table
WHERE jsondatavar:customer ::int= 1000

Here jsondatavar columns holds JSON string as a variant datatype. If we note I need to cast the result as double and filter as int.

Wanted to explore  alternatives  casting?

0 Kudos

Mounika_Tarigop
Databricks Employee
Databricks Employee

‎06-25-2024 03:54 PM

Could you please try using SQL functions: 

SELECT SUM(CAST(get_json_object(jsondatavar, '$.Value') AS DOUBLE)) AS total_value
FROM table
WHERE CAST(get_json_object(jsondatavar, '$.customer') AS INT) = 1000
0 Kudos