Snowflake SQL Tutorial 89 - Casting Out of VARIANT - Double Colon, TRY_TO_NUMBER, and why '1' is not 1

Snowflake SQL · Episode 89

In this post we are going to cover getting values out of a VARIANT with a type: the double colon, TO_VARCHAR, TRY_TO_NUMBER, and why the string "1" is not the number 1.

In the last post we learned how to reach a value inside a document. A path on its own gives you back a VARIANT, not a number and not a string. Casting is how you say what type you want, and the double colon is how you write it.

We will start with the three ways to get text out and build it up step by step. On the way we will meet a column whose MAX is not its largest value, a filter that agrees with the cast version half the time, and a total that changed without anything failing.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 89 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page
  1. Let's create some sample data
  2. Let's start with the three ways to get text out
  3. Now let's sort that column without a cast
  4. Now let's sort the same rows with a cast
  5. So what does that do to MIN and MAX?
  6. And what does it do to a threshold filter?
  7. Let's shrink that down to the smallest possible example
  8. So before you write a cast, find out what you are casting
  9. Now let's try a cast that cannot possibly work
  10. Now let's see what the safe form costs
  11. While we are here: does a cast round or chop?
  12. Too few decimals is quiet. Too few digits is not
  13. What about dates out of a VARIANT?
  14. Now booleans, and these are backwards
  15. So what does the number 1 do?
  16. How do we get round that?
  17. One last thing: the other casts, in both directions

Let's create some sample data

Everything on this page runs against the same four documents we have been using, plus one table of amounts written as plain text.

Sample data
-- Both tables come from the setup script this course uses throughout.
-- Run it once and every post in this level works against the same data.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

-- EVENTS_JSON - four JSON documents in a VARIANT column.
-- SALES_RAW   - the same kind of amounts, but written as TEXT by hand,
--               with currency symbols and thousands separators in them.

Let's start by looking at one path on all four rows, with TYPEOF beside it so we can see what we are dealing with.

Query
SELECT EVENT_ID,
       PAYLOAD:amount              AS AMOUNT_RAW,
       TYPEOF(PAYLOAD:amount)      AS ITS_TYPE
FROM EVENTS_JSON
ORDER BY EVENT_ID;
Result4 rows · 3 columns
EVENT_IDAMOUNT_RAWITS_TYPE
14200DECIMAL
2890.5DECIMAL
3"1500.00"VARCHAR
49250INTEGER

One path, three different types. Two rows are DECIMAL, one is INTEGER, and one is VARCHAR.

Look at the third row. The value prints with double quotes around it, because whoever wrote that document wrote the amount as a string. That is not a mistake in our sample data. It is the single most common thing you will find in a real JSON column, and the whole post comes out of it.

Let's start with the three ways to get text out

To get a value out with a type you cast it: two colons and a type name. Text is the interesting one, because there are three ways to ask for it and they do not all agree.

Let's run ::VARCHAR, TO_VARCHAR and TO_JSON side by side, with the length of two of the answers.

Query
SELECT EVENT_ID,
       PAYLOAD:amount                       AS AS_VARIANT,
       PAYLOAD:amount::VARCHAR              AS CAST_TO_VARCHAR,
       TO_VARCHAR(PAYLOAD:amount)           AS TO_VARCHAR_,
       TO_JSON(PAYLOAD:amount)              AS TO_JSON_,
       LENGTH(PAYLOAD:amount::VARCHAR)      AS CAST_LENGTH,
       LENGTH(TO_JSON(PAYLOAD:amount))      AS JSON_LENGTH
FROM EVENTS_JSON
ORDER BY EVENT_ID;
Result4 rows · 7 columns · scroll →
EVENT_IDAS_VARIANTCAST_TO_VARCHARTO_VARCHAR_TO_JSON_CAST_LENGTHJSON_LENGTH
1420042004200420044
2890.5890.5890.5890.555
3"1500.00"1500.001500.00"1500.00"79
4925092509250925044

On the plain numbers all three gave the same text and the two lengths match. Now look at the third row, where the value was already a string. The cast gave us seven characters and TO_JSON gave us nine.

Those two extra characters are the double quotes. In JSON the quotes are what make something a string, so TO_JSON keeps them.

Let's see the same thing on a value that really is text, and compare the two answers directly.

Query
SELECT PAYLOAD:user.name                    AS AS_VARIANT,
       PAYLOAD:user.name::VARCHAR           AS CAST_TO_VARCHAR,
       TO_VARCHAR(PAYLOAD:user.name)        AS TO_VARCHAR_,
       TO_JSON(PAYLOAD:user.name)           AS TO_JSON_,
       PAYLOAD:user.name::VARCHAR = TO_JSON(PAYLOAD:user.name) AS CAST_EQUALS_JSON
FROM EVENTS_JSON
WHERE EVENT_ID = 1;
Result1 row · 5 columns
AS_VARIANTCAST_TO_VARCHARTO_VARCHAR_TO_JSON_CAST_EQUALS_JSON
"Northwind Traders"Northwind TradersNorthwind Traders"Northwind Traders"FALSE

The cast drops the quotes. TO_VARCHAR drops the quotes. TO_JSON keeps them, and the last column confirms the two results are not equal.

Which one do I want?

Cast to VARCHAR when you want the value inside. Use TO_JSON when you want JSON text. They are two different jobs and the difference only shows up on strings, which is why it is so easy to get away with the wrong one for a while.

Now let's sort that column without a cast

Here is the part I want you to leave this post with. Let's order those four rows by the path itself, with no cast on it at all, and read the values down the page.

Query
SELECT EVENT_ID,
       PAYLOAD:amount              AS AMOUNT_RAW,
       TYPEOF(PAYLOAD:amount)      AS ITS_TYPE
FROM EVENTS_JSON
ORDER BY PAYLOAD:amount;
Result4 rows · 3 columns
EVENT_IDAMOUNT_RAWITS_TYPE
2890.5DECIMAL
14200DECIMAL
49250INTEGER
3"1500.00"VARCHAR

That is not a number sort. The smallest value came first and then it climbs, and then the last row is smaller than the one above it.

Now read the type column instead of the value column and it stops being mysterious. The two DECIMAL rows come first, then the INTEGER, then the VARCHAR.

Type first, value second

An uncast VARIANT compares by type first, and only uses the value to break ties inside a type. That one sentence explains the sort, the MAX and the filter in the next three sections. It is the single most useful thing to know about a VARIANT.

Now let's sort the same rows with a cast

Same four rows, same order by, and this time we cast the path to a number first.

Query
SELECT EVENT_ID,
       PAYLOAD:amount::NUMBER(12,2) AS AMOUNT_CAST,
       TYPEOF(PAYLOAD:amount)       AS ITS_TYPE
FROM EVENTS_JSON
ORDER BY PAYLOAD:amount::NUMBER(12,2);
Result4 rows · 3 columns
EVENT_IDAMOUNT_CASTITS_TYPE
2890.50DECIMAL
31500.00VARCHAR
14200.00DECIMAL
49250.00INTEGER

Now they behave. The values climb from smallest to largest and the type column is irrelevant, because every value being compared is a number.

Let's count how much actually moved between those two orderings, rather than eyeballing it.

Query
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(UNCAST_POS = CAST_POS) AS ROWS_IN_THE_SAME_PLACE,
       COUNT_IF(UNCAST_POS <> CAST_POS) AS ROWS_THAT_MOVED
FROM (SELECT EVENT_ID,
             ROW_NUMBER() OVER (ORDER BY PAYLOAD:amount)               AS UNCAST_POS,
             ROW_NUMBER() OVER (ORDER BY PAYLOAD:amount::NUMBER(12,2)) AS CAST_POS
      FROM EVENTS_JSON);
Result1 row · 3 columns
ROWS_TOTALROWS_IN_THE_SAME_PLACEROWS_THAT_MOVED
413

Four rows. One of them stayed where it was and three changed position. On a table this small that is easy to spot. On a report with a thousand rows and a LIMIT on the end, it is not.

So what does that do to MIN and MAX?

This is where type-first ordering stops being an oddity and starts being a bug in your numbers. Let's take the minimum and maximum of that column both ways.

Query
SELECT MIN(PAYLOAD:amount)                  AS MIN_UNCAST,
       MAX(PAYLOAD:amount)                  AS MAX_UNCAST,
       MIN(PAYLOAD:amount::NUMBER(12,2))    AS MIN_CAST,
       MAX(PAYLOAD:amount::NUMBER(12,2))    AS MAX_CAST,
       COUNT(*)                             AS ROWS_TOTAL
FROM EVENTS_JSON;
Result1 row · 5 columns
MIN_UNCASTMAX_UNCASTMIN_CASTMAX_CASTROWS_TOTAL
890.5"1500.00"890.509250.004

The uncast minimum is right. It is right by luck, because the smallest number happens to also be in the type that sorts first.

The uncast maximum is "1500.00". Quotes and all. And the cast maximum, from the same four rows, is 9250.00.

So the MAX of that column is not the largest value in it. The string outranks every number, so it wins the comparison no matter what it says.

And what does it do to a threshold filter?

Let's count the rows over a thousand and the rows under two thousand, with and without a cast, in one query.

Query
SELECT COUNT(*)                                            AS ROWS_TOTAL,
       COUNT_IF(PAYLOAD:amount > 1000)                     AS UNCAST_OVER_1000,
       COUNT_IF(PAYLOAD:amount::NUMBER(12,2) > 1000)       AS CAST_OVER_1000,
       COUNT_IF(PAYLOAD:amount < 2000)                     AS UNCAST_UNDER_2000,
       COUNT_IF(PAYLOAD:amount::NUMBER(12,2) < 2000)       AS CAST_UNDER_2000
FROM EVENTS_JSON;
Result1 row · 5 columns
ROWS_TOTALUNCAST_OVER_1000CAST_OVER_1000UNCAST_UNDER_2000CAST_UNDER_2000
43312

The two > 1000 counts agree. The two < 2000 counts do not.

Both of those follow from the same rule. A VARCHAR outranks every number, so the string row counts as greater than a thousand and the uncast answer happens to come out right. It also does not count as less than two thousand, because it is not being compared as a number at all, and there the uncast answer is one row short.

Why this one survives testing

Half of your predicates keep working. That is worse than all of them breaking, because a query that is wrong some of the time passes review, passes testing, and then disagrees with another query that nobody can explain.

Let's shrink that down to the smallest possible example

Two characters of difference is all it takes. Let's parse the text 1 and the text "1" and compare them.

Query
SELECT PARSE_JSON('1')                          AS JSON_NUMBER,
       PARSE_JSON('"1"')                        AS JSON_STRING,
       TYPEOF(PARSE_JSON('1'))                  AS NUMBER_TYPE,
       TYPEOF(PARSE_JSON('"1"'))                AS STRING_TYPE,
       PARSE_JSON('1') = PARSE_JSON('"1"')      AS ARE_THEY_EQUAL,
       PARSE_JSON('1')::INT = PARSE_JSON('"1"')::INT AS EQUAL_ONCE_CAST;
Result1 row · 6 columns · scroll →
JSON_NUMBERJSON_STRINGNUMBER_TYPESTRING_TYPEARE_THEY_EQUALEQUAL_ONCE_CAST
1"1"INTEGERVARCHARFALSETRUE

One is an INTEGER and the other is a VARCHAR. Are they equal? FALSE. So the string one is not the number one.

Cast them both to an integer and ask again, which is the last column. TRUE.

The cast is not decoration on the end of a path. It is what makes two values comparable in the first place.

Now the version you will actually meet: the same key written two ways by two source systems.

Query
SELECT PARSE_JSON('{"qty": 8}'):qty                     AS NUMBER_DOC,
       PARSE_JSON('{"qty": "8"}'):qty                   AS STRING_DOC,
       PARSE_JSON('{"qty": 8}'):qty = PARSE_JSON('{"qty": "8"}'):qty
         AS DOCS_AGREE,
       PARSE_JSON('{"qty": 8}'):qty::INT
         = PARSE_JSON('{"qty": "8"}'):qty::INT          AS AGREE_ONCE_CAST,
       PARSE_JSON('{"qty": 8}'):qty > 5                 AS NUMBER_OVER_FIVE,
       PARSE_JSON('{"qty": "8"}'):qty > 5               AS STRING_OVER_FIVE;
Result1 row · 6 columns · scroll →
NUMBER_DOCSTRING_DOCDOCS_AGREEAGREE_ONCE_CASTNUMBER_OVER_FIVESTRING_OVER_FIVE
8"8"FALSETRUETRUETRUE

One document says the quantity is a number and the other says it is a string. The documents do not agree. Cast both sides and they do.

Then look at the last two columns. Both of them are greater than five. So a > 5 filter would find both rows while an equality test would find neither, and that is the trap in one grid.

So before you write a cast, find out what you are casting

All of this is avoidable, and the way to avoid it is one query. Group by TYPEOF and let the column tell you what it contains.

Query
SELECT TYPEOF(PAYLOAD:amount)  AS ITS_TYPE,
       COUNT(*)                AS HOW_MANY,
       MIN(PAYLOAD:amount)     AS SMALLEST_SEEN,
       MAX(PAYLOAD:amount)     AS LARGEST_SEEN
FROM EVENTS_JSON
GROUP BY TYPEOF(PAYLOAD:amount)
ORDER BY HOW_MANY DESC;
Result3 rows · 4 columns
ITS_TYPEHOW_MANYSMALLEST_SEENLARGEST_SEEN
DECIMAL2890.54200
INTEGER192509250
VARCHAR1"1500.00""1500.00"

Two DECIMAL rows, one INTEGER and one VARCHAR. Run that before you write anything, and the string row stops being a surprise.

The smallest and largest columns are uncast on purpose, so the VARCHAR row shows its value with the quotes on. Within one type those two numbers are trustworthy, because everything being compared is the same type.

Now let's try a cast that cannot possibly work

Casting can fail. Let's take a document whose amount is the text N/A and cast it to a number.

Query
SELECT PARSE_JSON('{"amount":"N/A"}'):amount::NUMBER(12,2) AS SHOULD_NOT_GET_THIS_FAR;
Query failed — Snowflake returned an error
100071 (22000): Failed to cast variant value "N/A" to FIXED

It raises. The message names the value it could not convert, which is helpful, and the statement returns nothing, which is not.

So you reach for TRY_CAST, the way you would anywhere else in SQL. Let's try that on a variant holding a perfectly good number.

Query
SELECT TRY_CAST(PARSE_JSON('{"amount":1500}'):amount AS NUMBER(12,2))
         AS SHOULD_NOT_GET_THIS_FAR;
Query failed — Snowflake returned an error
001065 (22023): SQL compilation error:
Function TRY_CAST cannot be used with arguments of types VARIANT and NUMBER(12,2)

That is a compilation error, not a row error. TRY_CAST will not accept a VARIANT argument at all, and notice that the value in that query was 1500, a number that would convert without any trouble. So this has nothing to do with the data. The function does not take the type.

The way through is to turn the value into text first and then try the conversion on the text.

Query
SELECT PARSE_JSON('{"amount":"N/A"}'):amount                              AS RAW_,
       TRY_CAST(PARSE_JSON('{"amount":"N/A"}'):amount::VARCHAR AS NUMBER(12,2))
         AS TRY_CAST_GIVES,
       TRY_TO_NUMBER(PARSE_JSON('{"amount":"N/A"}'):amount::VARCHAR)
         AS TRY_TO_NUMBER_GIVES,
       TRY_TO_NUMBER(PARSE_JSON('{"amount":"1500.00"}'):amount::VARCHAR)
         AS ON_A_GOOD_ONE;
Result1 row · 4 columns
RAW_TRY_CAST_GIVESTRY_TO_NUMBER_GIVESON_A_GOOD_ONE
"N/A"NULLNULL1500

Now it works. TRY_CAST on the VARCHAR gave NULL instead of raising, and so did TRY_TO_NUMBER. On a value that does convert, the last column gave us a number back.

Go through text first

TRY_CAST is not useless here, it is just fussy about its argument. Hand it a VARIANT and you get a compile error. Hand it something::VARCHAR and it behaves exactly as you expected. Same for TRY_TO_NUMBER.

Now let's see what the safe form costs

TRY_TO_NUMBER is the safe form and it is not free. Let's look at it on a table of amounts that were written as text by hand, which is the messiest input in this course.

Query
SELECT ROW_ID, AMOUNT_TXT,
       TRY_TO_NUMBER(AMOUNT_TXT)                 AS TRY_GIVES,
       TRY_TO_NUMBER(AMOUNT_TXT) IS NULL         AS TRY_GAVE_NULL
FROM SALES_RAW
ORDER BY ROW_ID;
Result8 rows · 4 columns
ROW_IDAMOUNT_TXTTRY_GIVESTRY_GAVE_NULL
1$4,200.00NULLTRUE
2890.50891FALSE
3$6,150NULLTRUE
42,310.75NULLTRUE
5N/ANULLTRUE
6$640.00NULLTRUE
79,250.00NULLTRUE
8$1,080NULLTRUE

Eight rows, and seven of them came back NULL. A currency symbol stops it. A thousands separator stops it. The text N/A stops it, which is the one you would expect.

Now read the row that did convert. The text was 890.50 and the number that came back is 891.

That is the cost. TRY_TO_NUMBER with no precision and scale returns an integer, so the decimals are rounded away without anything failing. Let's measure what that does to a total.

Query
SELECT COUNT(*)                                                        AS ROWS_TOTAL,
       COUNT(TRY_TO_NUMBER(PAYLOAD:amount::VARCHAR))                   AS CONVERTED,
       COUNT(*) - COUNT(TRY_TO_NUMBER(PAYLOAD:amount::VARCHAR))        AS LOST,
       SUM(TRY_TO_NUMBER(PAYLOAD:amount::VARCHAR))                     AS SAFE_TOTAL,
       SUM(PAYLOAD:amount::NUMBER(12,2))                               AS DIRECT_TOTAL,
       SUM(TRY_TO_NUMBER(PAYLOAD:amount::VARCHAR))
         = SUM(PAYLOAD:amount::NUMBER(12,2))                           AS TOTALS_AGREE
FROM EVENTS_JSON;
Result1 row · 6 columns · scroll →
ROWS_TOTALCONVERTEDLOSTSAFE_TOTALDIRECT_TOTALTOTALS_AGREE
4401584115840.50FALSE

Four rows in, four rows converted, nothing lost. And the two totals disagree. The safe one is a whole number and the direct one has the pence on it.

The default is an integer

Nothing failed and nothing was dropped, and the answer still changed. If you use TRY_TO_NUMBER, give it a precision and a scale: TRY_TO_NUMBER(x::VARCHAR, 12, 2). Always, not when you remember.

While we are here: does a cast round or chop?

Worth knowing, because the two give different money. Let's cast the same value into two decimals, one decimal and none.

Query
SELECT PARSE_JSON('296.83'):: NUMBER(10,2)   AS TWO_DECIMALS,
       PARSE_JSON('296.83')::NUMBER(10,1)    AS ONE_DECIMAL,
       PARSE_JSON('296.83')::NUMBER(10,0)    AS NO_DECIMALS,
       PARSE_JSON('296.83')::NUMBER(10,1) = 296.8 AS ONE_DECIMAL_TRUNCATED,
       PARSE_JSON('296.83')::NUMBER(10,1) = 296.9 AS ONE_DECIMAL_ROUNDED;
Result1 row · 5 columns
TWO_DECIMALSONE_DECIMALNO_DECIMALSONE_DECIMAL_TRUNCATEDONE_DECIMAL_ROUNDED
296.83296.8297TRUEFALSE

Have a look at the fourth column. It is called ONE_DECIMAL_TRUNCATED and it came back TRUE, and it proves nothing at all. At one decimal, 296.83 gives 296.8 whether you round it or chop it, so that comparison cannot tell the two apart.

The column that settles it is the third one. To no decimals the answer is 297. It rounds.

Let's check that the same thing happens with ::INT, including on a negative number.

Query
SELECT PARSE_JSON('12.5'):: INT              AS TWELVE_POINT_FIVE,
       PARSE_JSON('12.4')::INT               AS TWELVE_POINT_FOUR,
       PARSE_JSON('12.6')::INT               AS TWELVE_POINT_SIX,
       PARSE_JSON('-12.5')::INT              AS MINUS_TWELVE_POINT_FIVE,
       PARSE_JSON('12.5')::INT = 13          AS MATCHES_ROUND,
       PARSE_JSON('12.5')::INT = 12          AS MATCHES_TRUNC;
Result1 row · 6 columns · scroll →
TWELVE_POINT_FIVETWELVE_POINT_FOURTWELVE_POINT_SIXMINUS_TWELVE_POINT_FIVEMATCHES_ROUNDMATCHES_TRUNC
131213-13TRUEFALSE

12.5 gives 13 and -12.5 gives -13. So a half goes away from zero in both directions, and the last two columns say that outright: it matches rounding and it does not match truncation.

And it does not matter whether the number arrived as a number or as a string.

Query
SELECT PARSE_JSON('"12.5"')::INT             AS STRING_TWELVE_FIVE,
       PARSE_JSON('"12.6"')::INT             AS STRING_TWELVE_SIX,
       PARSE_JSON('"12.5"')::INT = PARSE_JSON('12.5')::INT AS STRING_AGREES_WITH_NUMBER;
Result1 row · 3 columns
STRING_TWELVE_FIVESTRING_TWELVE_SIXSTRING_AGREES_WITH_NUMBER
1313TRUE

Same answers, and the last column confirms the string version agrees with the number version.

Too few decimals is quiet. Too few digits is not

That is the asymmetry worth remembering. Losing decimals happened silently above. Let's try losing whole digits instead, by casting a five-figure number into a type with room for three.

Query
SELECT PARSE_JSON('13100')::NUMBER(3,0) AS SHOULD_NOT_GET_THIS_FAR;
Query failed — Snowflake returned an error
100046 (22003): Number out of representable range: type FIXED, value 13100

Out of representable range, and the statement stops. So a scale that is too small changes your answer without telling you, and a precision that is too small refuses to run. Only one of those two is a problem you will find out about on your own.

What about dates out of a VARIANT?

JSON has no date type, so dates arrive as strings. Let's cast one directly, cast one through VARCHAR, and then try two that will not work.

Query
SELECT PARSE_JSON('{"d":"2024-01-15"}'):d                  AS AS_VARIANT,
       TYPEOF(PARSE_JSON('{"d":"2024-01-15"}'):d)           AS ITS_TYPE,
       PARSE_JSON('{"d":"2024-01-15"}'):d::DATE             AS CAST_TO_DATE,
       PARSE_JSON('{"d":"2024-01-15"}'):d::VARCHAR::DATE    AS VIA_VARCHAR,
       TRY_TO_DATE(PARSE_JSON('{"d":"15/01/2024"}'):d::VARCHAR) AS AMBIGUOUS_FORMAT,
       TRY_TO_DATE(PARSE_JSON('{"d":"not a date"}'):d::VARCHAR) AS NOT_A_DATE;
Result1 row · 6 columns · scroll →
AS_VARIANTITS_TYPECAST_TO_DATEVIA_VARCHARAMBIGUOUS_FORMATNOT_A_DATE
"2024-01-15"VARCHAR2024-01-152024-01-15NULLNULL

The type is VARCHAR, as expected. Casting straight to DATE works, and going through VARCHAR first gives the same answer.

The last two columns are the interesting ones. TRY_TO_DATE returned NULL for text that is clearly meant to be a date in day-first order, and NULL for text that is not a date at all. Two very different problems, one answer again.

Now booleans, and these are backwards

This is the part of the episode that genuinely surprised me. Let's start with the easy case: a JSON true.

Query
SELECT PARSE_JSON('true')                  AS JSON_TRUE,
       TYPEOF(PARSE_JSON('true'))          AS ITS_TYPE,
       PARSE_JSON('true')::BOOLEAN         AS CAST_TO_BOOLEAN,
       PARSE_JSON('false')::BOOLEAN        AS FALSE_CAST,
       PARSE_JSON('true')::BOOLEAN = TRUE  AS IT_IS_REALLY_TRUE;
Result1 row · 5 columns
JSON_TRUEITS_TYPECAST_TO_BOOLEANFALSE_CASTIT_IS_REALLY_TRUE
trueBOOLEANTRUEFALSETRUE

Its type is BOOLEAN and it casts to TRUE. No surprises there.

Now the word true in quotes, which is a string and not a boolean.

Query
SELECT PARSE_JSON('"true"')              AS JSON_STRING_TRUE,
       TYPEOF(PARSE_JSON('"true"'))      AS ITS_TYPE,
       PARSE_JSON('"true"')::BOOLEAN     AS CAST_TO_BOOLEAN;
Result1 row · 3 columns
JSON_STRING_TRUEITS_TYPECAST_TO_BOOLEAN
"true"VARCHARTRUE

Its type is VARCHAR and it still casts to TRUE. So Snowflake reads the word. That is reasonable enough.

How far does that go? Let's try the string yes.

Query
SELECT PARSE_JSON('"yes"')::BOOLEAN AS YES_AS_BOOLEAN;
Result1 row · 1 column
YES_AS_BOOLEAN
TRUE

Also true. And it keeps going.

Query
SELECT PARSE_JSON('"t"')::BOOLEAN      AS T_,
       PARSE_JSON('"y"')::BOOLEAN      AS Y_,
       PARSE_JSON('"on"')::BOOLEAN     AS ON_,
       PARSE_JSON('"1"')::BOOLEAN      AS STRING_ONE;
Result1 row · 4 columns
T_Y_ON_STRING_ONE
TRUETRUETRUETRUE

t, y, on and the string "1" all cast to TRUE. A lot of spellings are accepted, which makes the next result very hard to predict.

So what does the number 1 do?

The string "1" casts to TRUE. Let's try the number 1.

Query
SELECT PARSE_JSON('1')::BOOLEAN AS SHOULD_NOT_GET_THIS_FAR;
Query failed — Snowflake returned an error
100071 (22000): Failed to cast variant value 1 to BOOLEAN

It raises. The string casts and the number does not.

And zero behaves the same way, so it is not about the value.

Query
SELECT PARSE_JSON('0')::BOOLEAN AS SHOULD_NOT_GET_THIS_FAR;
Query failed — Snowflake returned an error
100071 (22000): Failed to cast variant value 0 to BOOLEAN

Now let's do the same casts in plain SQL, outside a VARIANT altogether.

Query
SELECT 1::BOOLEAN                       AS SQL_ONE_AS_BOOLEAN,
       0::BOOLEAN                       AS SQL_ZERO_AS_BOOLEAN,
       TO_BOOLEAN(1)                    AS TO_BOOLEAN_OF_ONE,
       TO_BOOLEAN(0)                    AS TO_BOOLEAN_OF_ZERO,
       '1'::BOOLEAN                     AS SQL_STRING_ONE,
       'true'::BOOLEAN                  AS SQL_STRING_TRUE;
Result1 row · 6 columns · scroll →
SQL_ONE_AS_BOOLEANSQL_ZERO_AS_BOOLEANTO_BOOLEAN_OF_ONETO_BOOLEAN_OF_ZEROSQL_STRING_ONESQL_STRING_TRUE
TRUEFALSETRUEFALSETRUETRUE

1::BOOLEAN is TRUE. 0::BOOLEAN is FALSE. TO_BOOLEAN of both is fine, and so are the strings.

So this is a VARIANT rule and not a SQL rule. The same cast, written against a plain number, works perfectly.

How do we get round that?

One extra step. Cast to a number or to text first, and then to boolean.

Query
SELECT PARSE_JSON('1')::INT                       AS AS_INT_FIRST,
       PARSE_JSON('1')::INT::BOOLEAN              AS THEN_BOOLEAN,
       PARSE_JSON('1')::VARCHAR                   AS AS_VARCHAR_FIRST,
       PARSE_JSON('1')::VARCHAR::BOOLEAN          AS VARCHAR_THEN_BOOLEAN,
       TRY_CAST(PARSE_JSON('1')::VARCHAR AS BOOLEAN) AS THE_SAFE_FORM;
Result1 row · 5 columns
AS_INT_FIRSTTHEN_BOOLEANAS_VARCHAR_FIRSTVARCHAR_THEN_BOOLEANTHE_SAFE_FORM
1TRUE1TRUETRUE

Through ::INT it works. Through ::VARCHAR it works. And TRY_CAST on the VARCHAR works too, which is the same lesson as the number cast earlier: go through text and TRY_CAST stops complaining.

And if the text might be anything, TRY_TO_BOOLEAN will tell you rather than raise.

Query
SELECT TRY_TO_BOOLEAN(PARSE_JSON('1')::VARCHAR)      AS FROM_NUMBER_ONE,
       TRY_TO_BOOLEAN(PARSE_JSON('"true"')::VARCHAR) AS FROM_STRING_TRUE,
       TRY_TO_BOOLEAN(PARSE_JSON('"maybe"')::VARCHAR) AS FROM_NONSENSE,
       TRY_TO_BOOLEAN(PARSE_JSON('2')::VARCHAR)      AS FROM_TWO,
       TRY_TO_BOOLEAN(PARSE_JSON('-1')::VARCHAR)     AS FROM_MINUS_ONE;
Result1 row · 5 columns
FROM_NUMBER_ONEFROM_STRING_TRUEFROM_NONSENSEFROM_TWOFROM_MINUS_ONE
TRUETRUENULLNULLNULL

The number one and the string true both came back TRUE. The word maybe, the number two and minus one all came back NULL. So 1 and 0 are accepted through text and other numbers are not.

One last thing: the other casts, in both directions

We have gone from a variant to text, to a number, to a date and to a boolean. Let's finish with the handful that are left, starting with a boolean turned into a number.

Query
SELECT PARSE_JSON('true')::INT AS SHOULD_NOT_GET_THIS_FAR;
Result1 row · 1 column
SHOULD_NOT_GET_THIS_FAR
1

The column name there is left over from an expectation that this would fail. It did not. A JSON true casts to 1 quite happily, which is the exact opposite of the number 1 refusing to become a boolean a moment ago.

In plain SQL that cast does the same thing.

Query
SELECT TRUE::INT AS SQL_TRUE_AS_INT, FALSE::INT AS SQL_FALSE_AS_INT;
Result1 row · 2 columns
SQL_TRUE_AS_INTSQL_FALSE_AS_INT
10

Now a JSON string that holds digits, cast to a number.

Query
SELECT PARSE_JSON('"42"')::INT           AS STRING_42_AS_INT,
       TYPEOF(PARSE_JSON('"42"'))        AS ITS_TYPE;
Result1 row · 2 columns
STRING_42_AS_INTITS_TYPE
42VARCHAR

That works, and the type column reminds us it was a VARCHAR on the way in.

And the other way, a JSON number cast to text.

Query
SELECT PARSE_JSON('42')::VARCHAR         AS NUMBER_42_AS_VARCHAR,
       TO_JSON(PARSE_JSON('42'))         AS AS_JSON_TEXT,
       LENGTH(PARSE_JSON('42')::VARCHAR) AS ITS_LENGTH;
Result1 row · 3 columns
NUMBER_42_AS_VARCHARAS_JSON_TEXTITS_LENGTH
42422

Both forms give the same text here, because a number has no quotes to keep. On a string they would differ, which is where this post started.

An array casts to text too.

Query
SELECT PARSE_JSON('[1,2]')::VARCHAR      AS ARRAY_AS_VARCHAR,
       TO_JSON(PARSE_JSON('[1,2]'))      AS ARRAY_AS_JSON_TEXT;
Result1 row · 2 columns
ARRAY_AS_VARCHARARRAY_AS_JSON_TEXT
[1,2][1,2]

One line of text, the same from both functions.

But an object will not become a number, and there is no sensible answer it could give.

Query
SELECT PARSE_JSON('{"a":1}')::INT AS SHOULD_NOT_GET_THIS_FAR;
Query failed — Snowflake returned an error
100071 (22000): Failed to cast variant value {"a":1} to FIXED

It raises, and the message quotes the document back at you. That is the right behaviour, and it is a good note to end on: the casts that refuse are the ones you can fix. It is the quiet ones that cost you.

Key points

  • An uncast VARIANT compares by type first and only by value second. That one sentence explains the sort, the MAX and the threshold filter on this page.
  • ORDER BY on an uncast path put a numeric string after every number, and three of our four rows changed position once the cast was added.
  • The MAX of our amount column was a quoted string while the largest number in it was a bigger value. Cast at the path and that goes away.
  • A > filter agreed with the cast version while a < filter did not. Half-working predicates are what make this hard to catch.
  • Three ways to get text out: ::VARCHAR, TO_VARCHAR and TO_JSON. Only TO_JSON keeps the JSON quotes, which on our string value was a difference of two characters.
  • The string "1" is not the number 1 until you cast them both. GROUP BY TYPEOF tells you in one query what the column really holds.
  • :: raises when the text will not convert, and TRY_CAST will not take a VARIANT argument at all — that is a compile error, not a row error.
  • The working safe form is to go through text: TRY_TO_NUMBER(x::VARCHAR). With no precision it returns an integer, so give it a precision and a scale.
  • Casting to fewer decimals rounds quietly, away from zero. Casting to fewer digits raises. Only one of those tells you.
  • Booleans are backwards out of a VARIANT. The strings true, yes, t, y, on and "1" all cast. The number 1 raises, and so does 0.
  • That is a VARIANT rule, not a SQL rule. 1::BOOLEAN in plain SQL is fine, and ::INT::BOOLEAN or ::VARCHAR::BOOLEAN makes it go away.
Next in this series

Episode 90 — FLATTEN. Arrays turned into rows, the parent that repeats on every child row, and the total that more than doubles without an error.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. The EVENTS_JSON and SALES_RAW data is invented demo data created by that script. Every count in the text above was read off the grid beside it rather than worked out here. Nothing on this page is timed and no performance or benchmark claims are made anywhere in this series. Conversion behaviour follows the Snowflake conversion functions documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)