Variant datatype

KSI — Tue, 25 Jun 2024 16:46:22 GMT

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?

Re: Variant datatype

Mounika_Tarigop — Tue, 25 Jun 2024 22:54:45 GMT

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

topic Variant datatype in Data Engineering

Variant datatype

Re: Variant datatype