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

Connect with Databricks Users in Your Area

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