cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Variant datatype

KSI
New Contributor II

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?

1 REPLY 1

Mounika_Tarigop
Databricks Employee
Databricks Employee

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