The Snowflake cast operator (::) explicitly converts an expression to a specified data type. When JSON keys are selected from a VARIANT column, casting makes values usable as relational columns, such as VARCHAR, DATE, or NUMBER. section 4.2
Use casts while flattening semistructured order data:
- Cast customer names to VARCHAR and order dates to DATE while selecting top-level JSON keys. section 4.2
- Cast nested delivery dates, baked-good types, and quantities after applying LATERAL FLATTEN. section 4.2
- Use TRY_TO_DATE or TRY_TO_NUMBER when malformed source values should become NULL instead of causing an error. section 4.2
Listing 4.2 Selecting values from keys at the highest level of the hierarchy
select customer_orders:"Customer"::varchar as customer, customer_orders:"Order date"::date as order_date, customer_orders:"Orders" from ORDERS_PARK_INN_RAW_STG;
Runnable SQL casts top-level JSON values into relational columns. section 4.2
Listing 4.4 Selecting values from JSON keys at the third level of the hierarchy
select customer_orders:"Customer"::varchar as customer, customer_orders:"Order date"::date as order_date, CO.value:"Delivery date"::date as delivery_date, DO.value:"Baked good type":: varchar as baked_good_type, DO.value:"Quantity"::number as quantity from ORDERS_PARK_INN_RAW_STG, lateral flatten (input => customer_orders:"Orders") CO, #1 lateral flatten (input => CO.value:"Orders by day") DO; #2
Runnable SQL applies casts while flattening nested order arrays. section 4.2
