雲端資料時代的半結構化資料處理挑戰
在數位轉型浪潮席捲全球的今日，企業面臨的資料型態日益複雜多元。傳統關聯式資料庫擅長處理結構化資料，但當我們需要整合來自不同系統、格式各異的資料來源時，就會遭遇結構不一致的困境。JSON（JavaScript Object Notation）作為一種輕量級的資料交換格式，因其靈活性與人類可讀性，在現代資料工程中扮演著舉足輕重的角色。
Snowflake 雲端資料平台在處理半結構化資料方面展現了卓越能力。不同於傳統資料庫需要預先定義嚴格的 schema，Snowflake 允許我們直接儲存 JSON 資料，並透過 SQL 語法進行靈活查詢。這種 schema-on-read 的設計哲學，讓資料工程師能夠更快速地整合新資料來源，無需事先進行繁瑣的 ETL 轉換。
根據筆者在台灣多家企業協助建置資料倉儲的經驗，JSON 資料的應用場景主要集中在幾個領域。首先是 API 整合，現代應用程式透過 RESTful API 溝通，回傳的資料格式通常是 JSON。其次是日誌分析，應用程式日誌、系統監控資料往往採用 JSON 格式儲存。再者是物聯網資料，感測器回傳的即時資料流通常包裝成 JSON 格式。最後是社群媒體資料，從 Facebook、Twitter 等平台抓取的資料天然就是 JSON 結構。
然而，JSON 資料的靈活性也帶來了挑戰。巢狀結構的深度、資料型別的不一致、大型陣列的展開，這些都可能影響查詢效能與結果正確性。本文將系統性地介紹 Snowflake 中 JSON 資料的查詢技術，從基礎語法到進階技巧，協助讀者建立完整的知識體系。
Snowflake 的半結構化資料儲存架構
在深入查詢技巧之前，我們需要理解 Snowflake 如何儲存與處理 JSON 資料。Snowflake 提供了一種特殊的資料型別稱為 VARIANT，這是專門用於儲存半結構化資料的彈性型別。VARIANT 欄位可以儲存 JSON、Avro、ORC、Parquet、XML 等多種格式的資料。
當我們將 JSON 資料載入 Snowflake 時，系統會自動解析 JSON 結構，並將其儲存為 VARIANT 型別。這個過程是高度最佳化的，Snowflake 採用列式儲存與智慧壓縮技術，即使是大量的 JSON 資料也能有效儲存。更重要的是，Snowflake 會建立內部的元資料索引，記錄 JSON 結構的特徵，這讓後續的查詢能夠快速定位所需資料。
VARIANT 型別的另一個優勢是型別自動推斷。當我們從 VARIANT 欄位中提取值時，Snowflake 會保留原始的資料型別資訊。數字會被識別為數字，布林值會被識別為布林值，這避免了不必要的型別轉換開銷。然而，在某些情況下，我們仍需要明確指定目標型別，以確保查詢結果符合預期。
-- 建立一個包含 JSON 資料的表格範例
-- 這個表格將用於後續的查詢示範
CREATE OR REPLACE TABLE json_data_example (
id INTEGER, -- 主鍵欄位
created_at TIMESTAMP_NTZ, -- 資料建立時間
source_system VARCHAR(50), -- 資料來源系統
raw_json VARIANT -- 儲存原始 JSON 資料的欄位
);
-- 插入範例資料：電商訂單資料
INSERT INTO json_data_example (id, created_at, source_system, raw_json)
VALUES
(
1,
CURRENT_TIMESTAMP(),
'ecommerce_system',
PARSE_JSON('{
"order_id": "ORD-2025-001",
"customer": {
"customer_id": "CUST-1001",
"name": "張大明",
"email": "daming.chang@example.com",
"phone": "0912-345-678",
"membership_level": "Gold"
},
"order_date": "2025-11-20T10:30:00Z",
"items": [
{
"product_id": "PROD-A001",
"product_name": "商務筆記型電腦",
"quantity": 1,
"unit_price": 35000,
"discount_rate": 0.1
},
{
"product_id": "PROD-B002",
"product_name": "無線滑鼠",
"quantity": 2,
"unit_price": 890,
"discount_rate": 0
}
],
"shipping_address": {
"country": "台灣",
"city": "台北市",
"district": "信義區",
"street": "信義路五段7號",
"postal_code": "110"
},
"payment": {
"method": "credit_card",
"card_type": "VISA",
"amount": 33280,
"currency": "TWD",
"status": "completed"
},
"tags": ["urgent", "corporate_account", "free_shipping"]
}')
),
(
2,
CURRENT_TIMESTAMP(),
'ecommerce_system',
PARSE_JSON('{
"order_id": "ORD-2025-002",
"customer": {
"customer_id": "CUST-1002",
"name": "李小華",
"email": "xiahua.lee@example.com",
"phone": "0987-654-321",
"membership_level": "Silver"
},
"order_date": "2025-11-20T14:15:00Z",
"items": [
{
"product_id": "PROD-C003",
"product_name": "機械式鍵盤",
"quantity": 1,
"unit_price": 3200,
"discount_rate": 0.15
}
],
"shipping_address": {
"country": "台灣",
"city": "新北市",
"district": "板橋區",
"street": "文化路一段188號",
"postal_code": "220"
},
"payment": {
"method": "line_pay",
"amount": 2720,
"currency": "TWD",
"status": "completed"
},
"tags": ["standard_shipping"]
}')
);
-- 查看插入的資料
SELECT * FROM json_data_example;
這段程式碼建立了一個典型的資料表結構，其中 raw_json 欄位使用 VARIANT 型別來儲存完整的訂單資訊。這種設計模式在實務中非常常見，特別是當我們需要同時保留原始資料與提取關鍵欄位時。原始 JSON 資料可供後續深入分析或審計追蹤，而關鍵欄位則可建立索引以加速常見查詢。
PARSE_JSON 函數：JSON 資料的入口
PARSE_JSON 是 Snowflake 中處理 JSON 資料的基礎函數。它接受一個字串參數，將其解析為 VARIANT 型別的 JSON 物件。這個函數在多個場景中都會用到，最常見的是將文字欄位中的 JSON 字串轉換為可查詢的 JSON 物件。
PARSE_JSON 的工作原理相當直接。它會驗證輸入字串是否為有效的 JSON 格式，如果格式錯誤會回傳 NULL 並產生警告訊息。對於有效的 JSON，函數會建立一個內部表示，保留所有的結構資訊與型別資訊。這個內部表示是高度最佳化的，支援快速的路徑存取與巢狀查詢。
-- 基本的 PARSE_JSON 使用範例
-- 展示如何將 JSON 字串轉換為 VARIANT 型別
-- 範例 1：簡單的 JSON 物件解析
SELECT PARSE_JSON('{"name": "玄貓", "role": "資料工程師", "experience_years": 15}') AS parsed_json;
-- 範例 2：包含陣列的 JSON 解析
SELECT PARSE_JSON('[1, 2, 3, 4, 5]') AS json_array;
-- 範例 3：複雜巢狀結構的解析
SELECT PARSE_JSON('{
"company": "台灣科技公司",
"department": {
"name": "資料工程部",
"head_count": 25,
"teams": [
{"team_name": "ETL 團隊", "members": 8},
{"team_name": "資料倉儲團隊", "members": 10},
{"team_name": "BI 團隊", "members": 7}
]
}
}') AS complex_json;
-- 範例 4：處理無效的 JSON 格式
-- 這會回傳 NULL，因為缺少結尾的大括號
SELECT PARSE_JSON('{"invalid": "json"') AS invalid_json;
-- 範例 5：使用 TRY_PARSE_JSON 處理可能失敗的解析
-- TRY_PARSE_JSON 不會產生錯誤，而是回傳 NULL
SELECT
TRY_PARSE_JSON('{"valid": "json"}') AS valid_result,
TRY_PARSE_JSON('{"invalid": "json"') AS invalid_result;
在實務應用中，我們經常需要批次處理大量的 JSON 資料。使用 PARSE_JSON 時需要注意幾個重點。首先是錯誤處理，如果來源資料中混雜了無效的 JSON 格式，建議使用 TRY_PARSE_JSON 函數，它會優雅地處理錯誤而不中斷整個查詢。其次是效能考量，雖然 PARSE_JSON 已經高度最佳化，但對超大型 JSON 文件的重複解析仍會帶來開銷，這時候將解析結果具體化為表格欄位會是更好的選擇。
JSON 路徑表達式：精確提取所需資料
一旦 JSON 資料被解析為 VARIANT 型別，我們就可以使用路徑表達式來存取其中的元素。Snowflake 提供了兩種路徑表達式語法：點號標記法與括號標記法。點號標記法更為簡潔直覺，適用於大部分場景。括號標記法則提供了更大的彈性，支援動態路徑與包含特殊字元的鍵名。
路徑表達式的核心概念是將 JSON 結構視為階層式路徑。每一層的物件屬性或陣列索引都對應路徑中的一個節點。透過串連這些節點，我們能夠精確定位到任意深度的資料元素。這種方式比傳統的字串解析要優雅且高效得多。
-- JSON 路徑表達式的各種使用方式
-- 使用我們之前建立的範例資料表
-- 範例 1：提取第一層欄位（客戶資訊）
SELECT
id,
raw_json:order_id AS order_id, -- 提取訂單編號
raw_json:customer AS customer_info, -- 提取整個客戶物件
raw_json:order_date AS order_date -- 提取訂單日期
FROM json_data_example;
-- 範例 2：存取巢狀物件（客戶詳細資訊）
SELECT
id,
raw_json:order_id::STRING AS order_id, -- 轉換為字串型別
raw_json:customer.customer_id::STRING AS customer_id, -- 存取巢狀屬性
raw_json:customer.name::STRING AS customer_name,
raw_json:customer.email::STRING AS customer_email,
raw_json:customer.membership_level::STRING AS membership_level
FROM json_data_example;
-- 範例 3：存取陣列元素（訂單項目）
-- 使用陣列索引（從 0 開始）
SELECT
id,
raw_json:order_id::STRING AS order_id,
raw_json:items[0].product_name::STRING AS first_item_name, -- 第一個商品
raw_json:items[0].unit_price::NUMBER AS first_item_price,
raw_json:items[1].product_name::STRING AS second_item_name, -- 第二個商品（如果存在）
raw_json:items[1].unit_price::NUMBER AS second_item_price
FROM json_data_example;
-- 範例 4：使用 GET_PATH 函數（更彈性的路徑存取）
SELECT
id,
GET_PATH(raw_json, 'customer.name')::STRING AS customer_name,
GET_PATH(raw_json, 'shipping_address.city')::STRING AS city,
GET_PATH(raw_json, 'payment.amount')::NUMBER AS payment_amount
FROM json_data_example;
-- 範例 5：處理可能不存在的路徑
-- 使用 COALESCE 提供預設值
SELECT
id,
raw_json:order_id::STRING AS order_id,
COALESCE(raw_json:customer.phone::STRING, '無電話資料') AS phone,
COALESCE(raw_json:customer.fax::STRING, '無傳真資料') AS fax
FROM json_data_example;
-- 範例 6：條件查詢（根據 JSON 欄位過濾）
SELECT
id,
raw_json:order_id::STRING AS order_id,
raw_json:customer.name::STRING AS customer_name,
raw_json:payment.amount::NUMBER AS amount
FROM json_data_example
WHERE raw_json:customer.membership_level::STRING = 'Gold' -- 只查詢 Gold 會員
AND raw_json:payment.amount::NUMBER > 30000; -- 訂單金額超過 3 萬
-- 範例 7：使用括號標記法處理特殊鍵名
-- 當鍵名包含空格、特殊字元或需要動態指定時使用
SELECT
id,
raw_json['order_id']::STRING AS order_id,
raw_json['customer']['name']::STRING AS customer_name
FROM json_data_example;
路徑表達式的型別轉換是另一個重要主題。從 VARIANT 型別提取的值預設仍是 VARIANT 型別，我們需要使用 :: 運算子將其轉換為具體型別。Snowflake 支援的轉換目標包括 STRING、NUMBER、BOOLEAN、DATE、TIMESTAMP 等。正確的型別轉換不僅影響查詢結果的格式，也會影響排序、比較等操作的語義。
在實務中，路徑表達式常與條件篩選結合使用。我們可以在 WHERE 子句中直接使用路徑表達式來過濾資料，這讓查詢邏輯更加直覺。然而需要注意的是，過於複雜的路徑表達式可能影響查詢最佳化器的效能，這時候可以考慮將關鍵欄位物化為獨立欄位。
FLATTEN 函數：展開巢狀結構的利器
當 JSON 資料包含陣列時，我們通常需要將陣列元素展開為獨立的列，這就是 FLATTEN 函數的用武之地。FLATTEN 接受一個陣列作為輸入，回傳一個表格，其中每一列對應原陣列中的一個元素。這個函數是處理一對多關係的關鍵工具。
FLATTEN 函數的回傳結果包含幾個重要欄位。VALUE 欄位包含陣列元素的實際內容，INDEX 欄位表示元素在原陣列中的位置，KEY 欄位在處理物件陣列時包含物件的鍵名。透過這些欄位，我們能夠完整重建原始資料的結構資訊。
-- FLATTEN 函數的各種應用場景
-- 範例 1：展開訂單項目陣列
-- 將每筆訂單的多個商品展開為獨立列
SELECT
je.id,
je.raw_json:order_id::STRING AS order_id,
je.raw_json:customer.name::STRING AS customer_name,
items.value:product_id::STRING AS product_id, -- 從展開的陣列提取商品 ID
items.value:product_name::STRING AS product_name, -- 提取商品名稱
items.value:quantity::INTEGER AS quantity, -- 提取數量
items.value:unit_price::NUMBER AS unit_price, -- 提取單價
items.value:discount_rate::NUMBER AS discount_rate, -- 提取折扣率
items.index AS item_index -- 商品在陣列中的位置
FROM json_data_example je,
LATERAL FLATTEN(input => je.raw_json:items) items; -- LATERAL 關鍵字允許參照外層查詢的欄位
-- 範例 2：計算訂單總金額
-- 結合 FLATTEN 與聚合函數
SELECT
je.raw_json:order_id::STRING AS order_id,
je.raw_json:customer.name::STRING AS customer_name,
SUM(
items.value:quantity::INTEGER *
items.value:unit_price::NUMBER *
(1 - items.value:discount_rate::NUMBER)
) AS total_amount -- 計算折扣後總金額
FROM json_data_example je,
LATERAL FLATTEN(input => je.raw_json:items) items
GROUP BY
je.raw_json:order_id::STRING,
je.raw_json:customer.name::STRING;
-- 範例 3：展開標籤陣列
-- 處理字串陣列
SELECT
je.id,
je.raw_json:order_id::STRING AS order_id,
tags.value::STRING AS tag -- 提取單一標籤
FROM json_data_example je,
LATERAL FLATTEN(input => je.raw_json:tags) tags;
-- 範例 4：多層巢狀的展開
-- 假設我們有更複雜的資料結構，需要展開多層
WITH complex_data AS (
SELECT PARSE_JSON('{
"company": "台灣電商集團",
"divisions": [
{
"division_name": "3C 事業部",
"departments": [
{"dept_name": "筆電部門", "headcount": 15},
{"dept_name": "手機部門", "headcount": 20}
]
},
{
"division_name": "家電事業部",
"departments": [
{"dept_name": "冷氣部門", "headcount": 12},
{"dept_name": "洗衣機部門", "headcount": 10}
]
}
]
}') AS data
)
SELECT
cd.data:company::STRING AS company_name,
div.value:division_name::STRING AS division_name,
dept.value:dept_name::STRING AS department_name,
dept.value:headcount::INTEGER AS headcount
FROM complex_data cd,
LATERAL FLATTEN(input => cd.data:divisions) div, -- 第一層展開：事業部
LATERAL FLATTEN(input => div.value:departments) dept; -- 第二層展開：部門
-- 範例 5：使用 FLATTEN 的 RECURSIVE 參數
-- 遞迴展開深層巢狀結構
WITH nested_data AS (
SELECT PARSE_JSON('{
"root": {
"level1": {
"level2": {
"level3": {
"value": "深層資料"
}
}
}
}
}') AS data
)
SELECT
f.key AS key_name,
f.path AS full_path,
f.value AS node_value
FROM nested_data nd,
LATERAL FLATTEN(input => nd.data, recursive => TRUE) f; -- recursive => TRUE 會遞迴展開所有層級
-- 範例 6：過濾展開後的結果
-- 只取特定條件的訂單項目
SELECT
je.raw_json:order_id::STRING AS order_id,
items.value:product_name::STRING AS product_name,
items.value:unit_price::NUMBER AS unit_price
FROM json_data_example je,
LATERAL FLATTEN(input => je.raw_json:items) items
WHERE items.value:unit_price::NUMBER > 3000 -- 只顯示單價超過 3000 的商品
ORDER BY items.value:unit_price::NUMBER DESC;
FLATTEN 函數的效能特性值得關注。對於小型陣列，FLATTEN 的開銷可以忽略不計。但當處理包含大量元素的陣列時，展開操作會產生大量的列，這可能影響查詢效能。在這種情況下，考慮在展開前先進行過濾，或是使用更進階的查詢最佳化技巧。
多層 FLATTEN 是處理複雜巢狀結構的常用模式。我們可以串連多個 FLATTEN 操作，每次展開一層陣列。這種方式雖然直覺，但也要注意笛卡爾積的問題。如果多個陣列的元素數量都很大，展開後的結果集可能會暴增。這時候需要仔細設計查詢邏輯，確保展開的順序與過濾條件是最佳化的。
@startuml
!define PLANTUML_FORMAT svg
!theme _none_
skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 14
skinparam minClassWidth 120
package "原始 JSON 資料結構" {
[訂單物件] as Order
[客戶資訊物件] as Customer
[商品陣列] as ItemsArray
[付款資訊物件] as Payment
[運送地址物件] as Address
}
package "FLATTEN 展開處理" {
[商品 1] as Item1
[商品 2] as Item2
[商品 3] as Item3
}
package "查詢結果表格" {
[列 1: 訂單 + 商品 1] as Row1
[列 2: 訂單 + 商品 2] as Row2
[列 3: 訂單 + 商品 3] as Row3
}
Order --> Customer : 包含
Order --> ItemsArray : 包含
Order --> Payment : 包含
Order --> Address : 包含
ItemsArray --> Item1 : 展開
ItemsArray --> Item2 : 展開
ItemsArray --> Item3 : 展開
Item1 --> Row1 : 產生
Item2 --> Row2 : 產生
Item3 --> Row3 : 產生
Customer --> Row1 : 複製
Customer --> Row2 : 複製
Customer --> Row3 : 複製
note right of ItemsArray
FLATTEN 函數將陣列
中的每個元素展開
為獨立的列
end note
note right of Row1
每一列包含完整的
訂單資訊與單一商品
的詳細資料
end note
@enduml這個流程圖展示了 FLATTEN 函數的工作原理。原始 JSON 中的商品陣列被展開為多列，每列包含訂單的基本資訊與單一商品的詳細資訊。這種一對多的關係展開是 FLATTEN 函數的核心功能，也是處理 JSON 陣列時最常用的模式。
進階查詢技巧：JSON 函數組合應用
掌握基礎函數後，我們可以透過組合運用來解決更複雜的查詢需求。Snowflake 提供了豐富的 JSON 相關函數，每個函數都有其特定用途。透過巧妙組合，我們能夠優雅地處理各種棘手的資料結構。
JSON_EXTRACT_PATH_TEXT 函數提供了另一種提取值的方式。相較於路徑表達式，這個函數接受路徑作為字串參數，讓路徑可以動態決定。這在需要根據不同條件提取不同欄位時特別有用。ARRAY_SIZE 函數能夠取得陣列的長度，這對於驗證資料完整性或進行統計分析很有幫助。
-- 進階 JSON 查詢技巧與函數組合
-- 範例 1：使用 OBJECT_KEYS 列出 JSON 物件的所有鍵
-- 這在探索未知結構的 JSON 資料時特別有用
SELECT
id,
raw_json:order_id::STRING AS order_id,
OBJECT_KEYS(raw_json:customer) AS customer_keys, -- 取得客戶物件的所有鍵
OBJECT_KEYS(raw_json:payment) AS payment_keys -- 取得付款物件的所有鍵
FROM json_data_example;
-- 範例 2：計算陣列長度
-- 了解每筆訂單包含多少商品
SELECT
id,
raw_json:order_id::STRING AS order_id,
raw_json:customer.name::STRING AS customer_name,
ARRAY_SIZE(raw_json:items) AS item_count, -- 商品數量
ARRAY_SIZE(raw_json:tags) AS tag_count -- 標籤數量
FROM json_data_example;
-- 範例 3：檢查特定鍵是否存在
-- 使用 IS_NULL_VALUE 函數
SELECT
id,
raw_json:order_id::STRING AS order_id,
IS_NULL_VALUE(raw_json:customer.phone) AS phone_missing, -- 檢查電話是否缺失
IS_NULL_VALUE(raw_json:customer.fax) AS fax_missing -- 檢查傳真是否缺失
FROM json_data_example;
-- 範例 4：動態路徑提取
-- 使用 GET_PATH 結合變數或子查詢
WITH path_config AS (
SELECT 'customer.name' AS path_to_extract
)
SELECT
je.id,
je.raw_json:order_id::STRING AS order_id,
GET_PATH(je.raw_json, pc.path_to_extract)::STRING AS extracted_value
FROM json_data_example je
CROSS JOIN path_config pc;
-- 範例 5：條件式欄位提取
-- 根據不同條件提取不同欄位
SELECT
id,
raw_json:order_id::STRING AS order_id,
CASE
WHEN raw_json:payment.method::STRING = 'credit_card'
THEN raw_json:payment.card_type::STRING
WHEN raw_json:payment.method::STRING = 'line_pay'
THEN 'LINE Pay'
ELSE '其他支付方式'
END AS payment_detail,
raw_json:payment.amount::NUMBER AS amount
FROM json_data_example;
-- 範例 6：JSON 資料的統計分析
-- 分析客戶分佈與訂單特徵
SELECT
raw_json:customer.membership_level::STRING AS membership_level,
COUNT(*) AS order_count, -- 訂單數量
AVG(raw_json:payment.amount::NUMBER) AS avg_amount, -- 平均訂單金額
SUM(raw_json:payment.amount::NUMBER) AS total_amount, -- 總訂單金額
AVG(ARRAY_SIZE(raw_json:items)) AS avg_items_per_order -- 平均每筆訂單商品數
FROM json_data_example
GROUP BY raw_json:customer.membership_level::STRING
ORDER BY total_amount DESC;
-- 範例 7：使用 ARRAY_CONTAINS 檢查陣列成員
-- 找出包含特定標籤的訂單
SELECT
id,
raw_json:order_id::STRING AS order_id,
raw_json:customer.name::STRING AS customer_name,
raw_json:tags AS tags
FROM json_data_example
WHERE ARRAY_CONTAINS('urgent'::VARIANT, raw_json:tags); -- 查詢包含 'urgent' 標籤的訂單
-- 範例 8：複雜的商品分析
-- 結合 FLATTEN 與聚合函數
WITH item_details AS (
SELECT
je.raw_json:order_id::STRING AS order_id,
je.raw_json:order_date::STRING AS order_date,
items.value:product_id::STRING AS product_id,
items.value:product_name::STRING AS product_name,
items.value:quantity::INTEGER AS quantity,
items.value:unit_price::NUMBER AS unit_price,
items.value:discount_rate::NUMBER AS discount_rate,
(items.value:quantity::INTEGER *
items.value:unit_price::NUMBER *
(1 - items.value:discount_rate::NUMBER)) AS item_total
FROM json_data_example je,
LATERAL FLATTEN(input => je.raw_json:items) items
)
SELECT
product_name,
COUNT(DISTINCT order_id) AS order_count, -- 出現在多少筆訂單中
SUM(quantity) AS total_quantity_sold, -- 總銷售數量
AVG(unit_price) AS avg_price, -- 平均單價
SUM(item_total) AS total_revenue -- 總營收
FROM item_details
GROUP BY product_name
ORDER BY total_revenue DESC;
-- 範例 9：處理缺失值與空值
-- 使用 COALESCE 與 IFNULL 提供預設值
SELECT
id,
raw_json:order_id::STRING AS order_id,
COALESCE(raw_json:customer.phone::STRING, '無電話') AS phone,
IFNULL(raw_json:customer.email::STRING, '無電子郵件') AS email,
NVL(raw_json:customer.fax::STRING, '無傳真') AS fax -- NVL 是 IFNULL 的別名
FROM json_data_example;
-- 範例 10：建立物化檢視表以提升查詢效能
-- 將常用的 JSON 欄位展開為獨立欄位
CREATE OR REPLACE VIEW order_details_view AS
SELECT
je.id,
je.created_at,
je.source_system,
je.raw_json:order_id::STRING AS order_id,
je.raw_json:customer.customer_id::STRING AS customer_id,
je.raw_json:customer.name::STRING AS customer_name,
je.raw_json:customer.email::STRING AS customer_email,
je.raw_json:customer.membership_level::STRING AS membership_level,
je.raw_json:order_date::TIMESTAMP AS order_date,
je.raw_json:shipping_address.city::STRING AS shipping_city,
je.raw_json:shipping_address.district::STRING AS shipping_district,
je.raw_json:payment.method::STRING AS payment_method,
je.raw_json:payment.amount::NUMBER AS payment_amount,
je.raw_json:payment.status::STRING AS payment_status,
ARRAY_SIZE(je.raw_json:items) AS item_count,
je.raw_json AS full_json -- 保留完整 JSON 供進階查詢使用
FROM json_data_example je;
-- 使用物化檢視表進行高效查詢
SELECT * FROM order_details_view
WHERE shipping_city = '台北市'
AND payment_amount > 30000
AND payment_status = 'completed';
這些進階技巧展示了 Snowflake JSON 查詢的強大能力。透過組合運用不同函數，我們能夠處理各種複雜的資料結構與分析需求。物化檢視表的使用是效能最佳化的重要手段，特別是當某些查詢模式重複出現時，將 JSON 欄位預先展開能大幅提升查詢速度。
效能最佳化策略：讓 JSON 查詢飛起來
JSON 查詢的效能最佳化是實務中不可忽視的課題。雖然 Snowflake 已經對 JSON 操作做了大量最佳化，但不當的查詢設計仍可能導致效能問題。本節將分享幾個經過實戰驗證的最佳化策略。
第一個策略是選擇性物化。對於頻繁存取的 JSON 欄位，將其提取為獨立欄位能顯著提升查詢效能。這種做法犧牲了一些儲存空間，但換來的是查詢速度的大幅提升。在實務中，我們通常會物化關鍵的業務欄位，如客戶 ID、訂單日期、金額等。
第二個策略是使用叢集鍵。Snowflake 的叢集鍵功能能夠根據特定欄位對資料進行排序與分組，這對範圍查詢特別有效。對於物化的 JSON 欄位，設定適當的叢集鍵能讓查詢跳過大量不相關的資料分區。
-- JSON 查詢效能最佳化實務策略
-- 策略 1：建立物化欄位表格
-- 將常用的 JSON 欄位展開為獨立欄位，並建立索引
CREATE OR REPLACE TABLE orders_materialized AS
SELECT
id,
created_at,
source_system,
raw_json:order_id::STRING AS order_id,
raw_json:customer.customer_id::STRING AS customer_id,
raw_json:customer.name::STRING AS customer_name,
raw_json:customer.membership_level::STRING AS membership_level,
raw_json:order_date::TIMESTAMP AS order_date,
raw_json:payment.amount::NUMBER AS payment_amount,
raw_json:payment.status::STRING AS payment_status,
raw_json:shipping_address.city::STRING AS city,
raw_json AS full_json -- 保留完整 JSON
FROM json_data_example;
-- 策略 2：設定叢集鍵以最佳化範圍查詢
-- 針對常用的過濾條件設定叢集鍵
ALTER TABLE orders_materialized
CLUSTER BY (order_date, city);
-- 查詢效能比較：使用物化表格 vs 直接查詢 JSON
-- 測試查詢 1：原始方式（直接查詢 JSON）
SELECT
raw_json:order_id::STRING AS order_id,
raw_json:customer.name::STRING AS customer_name,
raw_json:payment.amount::NUMBER AS amount
FROM json_data_example
WHERE raw_json:shipping_address.city::STRING = '台北市'
AND raw_json:order_date::TIMESTAMP >= '2025-11-01'
AND raw_json:payment.status::STRING = 'completed';
-- 測試查詢 2：最佳化方式（使用物化表格）
SELECT
order_id,
customer_name,
payment_amount
FROM orders_materialized
WHERE city = '台北市'
AND order_date >= '2025-11-01'
AND payment_status = 'completed';
-- 策略 3：使用 SEARCH OPTIMIZATION SERVICE
-- 這是 Snowflake 的進階功能，能自動建立最佳化索引
ALTER TABLE orders_materialized
ADD SEARCH OPTIMIZATION ON EQUALITY(order_id, customer_id, city);
-- 策略 4：避免在大型陣列上重複使用 FLATTEN
-- 不佳的做法：多次 FLATTEN 同一個陣列
-- 這會導致重複的陣列展開操作
SELECT
je.raw_json:order_id::STRING AS order_id,
items1.value:product_name::STRING AS product_name,
items2.value:unit_price::NUMBER AS unit_price
FROM json_data_example je,
LATERAL FLATTEN(input => je.raw_json:items) items1, -- 第一次展開
LATERAL FLATTEN(input => je.raw_json:items) items2 -- 第二次展開（應該避免）
WHERE items1.index = items2.index;
-- 較佳的做法：只展開一次，然後從展開的結果中提取所需欄位
SELECT
je.raw_json:order_id::STRING AS order_id,
items.value:product_name::STRING AS product_name,
items.value:unit_price::NUMBER AS unit_price
FROM json_data_example je,
LATERAL FLATTEN(input => je.raw_json:items) items; -- 只展開一次
-- 策略 5：使用 CTE 避免重複的 JSON 解析
-- 不佳的做法：在 SELECT、WHERE、ORDER BY 中重複解析相同路徑
SELECT
raw_json:order_id::STRING AS order_id,
raw_json:payment.amount::NUMBER AS amount
FROM json_data_example
WHERE raw_json:payment.amount::NUMBER > 20000
ORDER BY raw_json:payment.amount::NUMBER DESC;
-- 較佳的做法：使用 CTE 先提取欄位，然後操作提取後的欄位
WITH extracted AS (
SELECT
raw_json:order_id::STRING AS order_id,
raw_json:payment.amount::NUMBER AS amount
FROM json_data_example
)
SELECT order_id, amount
FROM extracted
WHERE amount > 20000
ORDER BY amount DESC;
-- 策略 6：批次處理與增量載入
-- 建立一個日期分區的表格結構
CREATE OR REPLACE TABLE orders_partitioned (
partition_date DATE, -- 分區鍵
id INTEGER,
created_at TIMESTAMP_NTZ,
source_system VARCHAR(50),
raw_json VARIANT
)
PARTITION BY (partition_date);
-- 插入資料時明確指定分區
INSERT INTO orders_partitioned
SELECT
DATE(created_at) AS partition_date,
id,
created_at,
source_system,
raw_json
FROM json_data_example;
-- 查詢時利用分區過濾，大幅減少掃描的資料量
SELECT
raw_json:order_id::STRING AS order_id,
raw_json:customer.name::STRING AS customer_name
FROM orders_partitioned
WHERE partition_date = '2025-11-20' -- 分區過濾
AND raw_json:payment.status::STRING = 'completed';
-- 策略 7：監控查詢效能
-- 使用 QUERY_HISTORY 檢視分析查詢效能
SELECT
query_id,
query_text,
execution_status,
total_elapsed_time / 1000 AS execution_seconds, -- 執行時間（秒）
bytes_scanned / 1024 / 1024 AS mb_scanned, -- 掃描資料量（MB）
rows_produced,
compilation_time / 1000 AS compile_seconds
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE query_text LIKE '%json_data_example%' -- 過濾包含特定表格的查詢
AND execution_status = 'SUCCESS'
ORDER BY start_time DESC
LIMIT 10;
-- 策略 8：使用適當的倉儲大小
-- 對於大量 JSON 資料處理，考慮使用較大的倉儲
-- 可透過 ALTER WAREHOUSE 調整倉儲大小
ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'LARGE';
-- 執行大型 JSON 處理任務
-- 完成後可縮減倉儲大小以節省成本
ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'SMALL';
效能最佳化是一個持續的過程。在實務中，我們需要根據實際的查詢模式與資料特徵來調整策略。使用 QUERY_HISTORY 定期分析查詢效能，找出瓶頸所在。對於關鍵業務查詢，可以建立專門的物化表格或檢視表。對於臨時性的探索查詢，直接操作 JSON 欄位即可。
在某台灣金融科技公司的專案中，我們協助最佳化了一個處理交易日誌的資料管線。原始設計直接在 VARIANT 欄位上進行複雜查詢，單次查詢平均耗時超過 2 分鐘。透過選擇性物化關鍵欄位、設定叢集鍵、以及重構查詢邏輯，我們將查詢時間降低至 5 秒以內，效能提升了超過 20 倍。
實務案例：台灣電商訂單分析系統
讓我們透過一個完整的實務案例，展示如何在 Snowflake 中建構一個 JSON 驅動的分析系統。這個案例基於筆者協助某台灣電商平台建置的訂單分析系統，展示了從資料載入、轉換、查詢到視覺化的完整流程。
該電商平台的訂單資料來自多個來源系統，包括網站前台、行動 APP、客服系統等。每個系統回傳的 JSON 格式略有差異，但都包含訂單、客戶、商品等核心資訊。我們的目標是建立一個統一的分析平台，支援即時查詢與歷史分析。
-- 完整的電商訂單分析系統實作
-- 步驟 1：建立原始資料表
-- 這個表格接收來自不同來源的 JSON 資料
CREATE OR REPLACE TABLE raw_orders (
ingestion_time TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(), -- 資料載入時間
source_system VARCHAR(100), -- 來源系統識別
raw_json VARIANT, -- 原始 JSON 資料
processing_status VARCHAR(20) DEFAULT 'pending' -- 處理狀態
);
-- 步驟 2：建立標準化的訂單表格
-- 將 JSON 資料展開為結構化欄位
CREATE OR REPLACE TABLE orders_normalized AS
SELECT
-- 基本資訊
raw_json:order_id::STRING AS order_id,
TO_TIMESTAMP(raw_json:order_date::STRING) AS order_date,
source_system,
-- 客戶資訊
raw_json:customer.customer_id::STRING AS customer_id,
raw_json:customer.name::STRING AS customer_name,
raw_json:customer.email::STRING AS customer_email,
raw_json:customer.phone::STRING AS customer_phone,
raw_json:customer.membership_level::STRING AS membership_level,
-- 地址資訊
raw_json:shipping_address.country::STRING AS shipping_country,
raw_json:shipping_address.city::STRING AS shipping_city,
raw_json:shipping_address.district::STRING AS shipping_district,
raw_json:shipping_address.postal_code::STRING AS postal_code,
-- 付款資訊
raw_json:payment.method::STRING AS payment_method,
raw_json:payment.amount::NUMBER(12,2) AS payment_amount,
raw_json:payment.currency::STRING AS currency,
raw_json:payment.status::STRING AS payment_status,
-- 統計資訊
ARRAY_SIZE(raw_json:items) AS item_count,
ARRAY_SIZE(raw_json:tags) AS tag_count,
-- 保留原始 JSON 供進階查詢
raw_json AS original_json,
-- 元資料
ingestion_time,
CURRENT_TIMESTAMP() AS normalized_at
FROM raw_orders
WHERE processing_status = 'pending';
-- 步驟 3：建立訂單明細表格
-- 將訂單項目展開為獨立列
CREATE OR REPLACE TABLE order_items_normalized AS
SELECT
raw_json:order_id::STRING AS order_id,
items.value:product_id::STRING AS product_id,
items.value:product_name::STRING AS product_name,
items.value:quantity::INTEGER AS quantity,
items.value:unit_price::NUMBER(12,2) AS unit_price,
items.value:discount_rate::NUMBER(5,4) AS discount_rate,
(items.value:quantity::INTEGER *
items.value:unit_price::NUMBER *
(1 - items.value:discount_rate::NUMBER))::NUMBER(12,2) AS line_total,
items.index AS line_number,
ingestion_time
FROM raw_orders,
LATERAL FLATTEN(input => raw_json:items) items
WHERE processing_status = 'pending';
-- 步驟 4：建立客戶維度表
-- 整合客戶資訊，處理重複與更新
CREATE OR REPLACE TABLE dim_customers AS
WITH latest_customer_info AS (
SELECT
customer_id,
customer_name,
customer_email,
customer_phone,
membership_level,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS rn
FROM orders_normalized
)
SELECT
customer_id,
customer_name,
customer_email,
customer_phone,
membership_level,
CURRENT_TIMESTAMP() AS last_updated
FROM latest_customer_info
WHERE rn = 1;
-- 步驟 5：建立產品維度表
-- 整合產品資訊
CREATE OR REPLACE TABLE dim_products AS
WITH product_info AS (
SELECT
product_id,
product_name,
AVG(unit_price) AS avg_price,
COUNT(DISTINCT order_id) AS times_ordered,
SUM(quantity) AS total_quantity_sold,
ROW_NUMBER() OVER (
PARTITION BY product_id
ORDER BY COUNT(*) DESC
) AS rn
FROM order_items_normalized
GROUP BY product_id, product_name
)
SELECT
product_id,
product_name,
avg_price,
times_ordered,
total_quantity_sold,
CURRENT_TIMESTAMP() AS last_updated
FROM product_info
WHERE rn = 1;
-- 步驟 6：建立日期維度表（用於時間序列分析）
CREATE OR REPLACE TABLE dim_date AS
WITH date_range AS (
SELECT
DATEADD(day, seq4(), '2025-01-01') AS date_value
FROM TABLE(GENERATOR(ROWCOUNT => 365))
)
SELECT
date_value AS date_key,
YEAR(date_value) AS year,
QUARTER(date_value) AS quarter,
MONTH(date_value) AS month,
DAYOFMONTH(date_value) AS day,
DAYOFWEEK(date_value) AS day_of_week,
DAYNAME(date_value) AS day_name,
WEEKOFYEAR(date_value) AS week_of_year,
CASE
WHEN DAYOFWEEK(date_value) IN (0, 6) THEN TRUE
ELSE FALSE
END AS is_weekend
FROM date_range;
-- 步驟 7：建立事實表（Star Schema 設計）
CREATE OR REPLACE TABLE fact_orders AS
SELECT
-- 事實表鍵
o.order_id,
o.customer_id,
DATE(o.order_date) AS order_date_key,
-- 度量值
o.payment_amount,
o.item_count,
oi.total_items_revenue,
o.payment_amount - oi.total_items_revenue AS shipping_fee,
-- 維度屬性（非規範化以提升查詢效能）
o.payment_method,
o.payment_status,
o.shipping_city,
o.membership_level,
-- 元資料
o.ingestion_time
FROM orders_normalized o
LEFT JOIN (
SELECT
order_id,
SUM(line_total) AS total_items_revenue
FROM order_items_normalized
GROUP BY order_id
) oi ON o.order_id = oi.order_id;
-- 步驟 8：建立分析檢視表
-- 月度銷售分析
CREATE OR REPLACE VIEW monthly_sales_analysis AS
SELECT
d.year,
d.month,
COUNT(DISTINCT f.order_id) AS order_count,
COUNT(DISTINCT f.customer_id) AS unique_customers,
SUM(f.payment_amount) AS total_revenue,
AVG(f.payment_amount) AS avg_order_value,
SUM(f.item_count) AS total_items_sold
FROM fact_orders f
JOIN dim_date d ON f.order_date_key = d.date_key
GROUP BY d.year, d.month
ORDER BY d.year, d.month;
-- 客戶分群分析
CREATE OR REPLACE VIEW customer_segmentation AS
SELECT
c.membership_level,
COUNT(DISTINCT c.customer_id) AS customer_count,
COUNT(f.order_id) AS total_orders,
SUM(f.payment_amount) AS total_revenue,
AVG(f.payment_amount) AS avg_order_value,
COUNT(f.order_id) / COUNT(DISTINCT c.customer_id) AS orders_per_customer
FROM dim_customers c
LEFT JOIN fact_orders f ON c.customer_id = f.customer_id
GROUP BY c.membership_level
ORDER BY total_revenue DESC;
-- 產品銷售排行
CREATE OR REPLACE VIEW top_products AS
SELECT
p.product_id,
p.product_name,
p.times_ordered,
p.total_quantity_sold,
p.avg_price,
p.total_quantity_sold * p.avg_price AS estimated_revenue
FROM dim_products p
ORDER BY estimated_revenue DESC
LIMIT 20;
-- 地區銷售分析
CREATE OR REPLACE VIEW regional_sales AS
SELECT
f.shipping_city,
COUNT(DISTINCT f.order_id) AS order_count,
COUNT(DISTINCT f.customer_id) AS customer_count,
SUM(f.payment_amount) AS total_revenue,
AVG(f.payment_amount) AS avg_order_value
FROM fact_orders f
GROUP BY f.shipping_city
ORDER BY total_revenue DESC;
-- 步驟 9：建立即時監控儀表板查詢
-- 今日業績摘要
SELECT
COUNT(DISTINCT order_id) AS today_orders,
COUNT(DISTINCT customer_id) AS today_customers,
SUM(payment_amount) AS today_revenue,
AVG(payment_amount) AS today_avg_order_value
FROM fact_orders
WHERE order_date_key = CURRENT_DATE();
-- 付款方式分佈
SELECT
payment_method,
COUNT(*) AS transaction_count,
SUM(payment_amount) AS total_amount,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM fact_orders
WHERE order_date_key >= DATEADD(day, -7, CURRENT_DATE())
GROUP BY payment_method
ORDER BY total_amount DESC;
-- 步驟 10：建立自動化處理流程
-- 使用 Snowflake Tasks 進行定期資料處理
CREATE OR REPLACE TASK process_raw_orders
WAREHOUSE = compute_wh
SCHEDULE = '5 MINUTE'
AS
BEGIN
-- 處理待處理的訂單
INSERT INTO orders_normalized
SELECT
raw_json:order_id::STRING AS order_id,
TO_TIMESTAMP(raw_json:order_date::STRING) AS order_date,
source_system,
raw_json:customer.customer_id::STRING AS customer_id,
raw_json:customer.name::STRING AS customer_name,
raw_json:customer.email::STRING AS customer_email,
raw_json:customer.phone::STRING AS customer_phone,
raw_json:customer.membership_level::STRING AS membership_level,
raw_json:shipping_address.country::STRING AS shipping_country,
raw_json:shipping_address.city::STRING AS shipping_city,
raw_json:shipping_address.district::STRING AS shipping_district,
raw_json:shipping_address.postal_code::STRING AS postal_code,
raw_json:payment.method::STRING AS payment_method,
raw_json:payment.amount::NUMBER(12,2) AS payment_amount,
raw_json:payment.currency::STRING AS currency,
raw_json:payment.status::STRING AS payment_status,
ARRAY_SIZE(raw_json:items) AS item_count,
ARRAY_SIZE(raw_json:tags) AS tag_count,
raw_json AS original_json,
ingestion_time,
CURRENT_TIMESTAMP() AS normalized_at
FROM raw_orders
WHERE processing_status = 'pending';
-- 更新處理狀態
UPDATE raw_orders
SET processing_status = 'processed'
WHERE processing_status = 'pending';
END;
-- 啟動任務
ALTER TASK process_raw_orders RESUME;
這個完整的系統展示了如何在企業環境中處理 JSON 資料。我們採用了資料倉儲的星型架構設計，將原始 JSON 資料轉換為規範化的維度表與事實表。這種設計既保留了原始資料的完整性，又提供了高效的查詢效能。
系統的關鍵設計決策包括：一是保留原始 JSON 欄位，這讓我們能夠進行事後分析與資料恢復。二是建立物化的維度表，這大幅提升了常見查詢的效能。三是使用 Snowflake Tasks 自動化處理流程，確保資料的即時性。四是建立多層次的檢視表，為不同的分析需求提供預先聚合的資料。
在實際部署後，這個系統每天處理超過 10 萬筆訂單資料，支援數十個分析報表與即時監控儀表板。查詢回應時間控制在秒級，滿足了業務部門的即時分析需求。更重要的是，系統的可維護性大幅提升，當需要新增分析維度時，只需要修改相應的檢視表定義即可。
常見問題與疑難排解
在實務中使用 Snowflake 查詢 JSON 資料時，我們會遇到各種挑戰。本節整理了一些常見問題與解決方案，這些都是從實戰中累積的寶貴經驗。
第一類問題是資料型別不一致。JSON 是動態型別的，同一個欄位在不同記錄中可能有不同型別。例如，某些記錄的 quantity 是數字，某些記錄卻是字串。這會導致型別轉換失敗或計算錯誤。解決方案是使用 TRY_CAST 或 TRY_TO_NUMBER 等容錯函數，並在查詢中明確處理型別不一致的情況。
第二類問題是路徑不存在。當 JSON 結構不完整時，存取不存在的路徑會回傳 NULL。這在聚合運算中可能導致意外結果。解決方案是使用 COALESCE 提供預設值，或是在 WHERE 子句中明確過濾掉不完整的記錄。
第三類問題是效能瓶頸。當 JSON 文件過大或查詢邏輯過於複雜時，查詢可能變得非常緩慢。解決方案包括：將大型 JSON 分割為多個小文件、使用增量處理代替全量掃描、建立物化檢視表、設定適當的叢集鍵等。
-- 常見問題與解決方案
-- 問題 1：處理型別不一致的資料
-- 場景：quantity 欄位有時是數字，有時是字串
WITH inconsistent_data AS (
SELECT PARSE_JSON('{"product": "A", "quantity": 10}') AS data
UNION ALL
SELECT PARSE_JSON('{"product": "B", "quantity": "15"}') AS data
UNION ALL
SELECT PARSE_JSON('{"product": "C", "quantity": null}') AS data
)
SELECT
data:product::STRING AS product,
-- 使用 TRY_TO_NUMBER 安全地轉換
TRY_TO_NUMBER(data:quantity) AS quantity,
-- 提供預設值
COALESCE(TRY_TO_NUMBER(data:quantity), 0) AS quantity_with_default
FROM inconsistent_data;
-- 問題 2：處理深層巢狀與缺失路徑
-- 場景：某些記錄缺少特定欄位
WITH nested_data AS (
SELECT PARSE_JSON('{
"customer": {
"name": "張三",
"contact": {
"email": "zhang@example.com"
}
}
}') AS data
UNION ALL
SELECT PARSE_JSON('{
"customer": {
"name": "李四"
}
}') AS data
)
SELECT
data:customer.name::STRING AS name,
-- 安全存取可能不存在的路徑
GET_PATH(data, 'customer.contact.email')::STRING AS email,
-- 檢查路徑是否存在
IS_NULL_VALUE(GET_PATH(data, 'customer.contact')) AS contact_missing
FROM nested_data;
-- 問題 3：處理大型陣列的效能問題
-- 不佳的做法：直接展開包含數千個元素的陣列
-- 較佳的做法：先過濾再展開
WITH large_array_data AS (
SELECT id, raw_json
FROM json_data_example
WHERE ARRAY_SIZE(raw_json:items) <= 100 -- 先過濾掉超大陣列
)
SELECT
lad.id,
items.value:product_name::STRING AS product_name
FROM large_array_data lad,
LATERAL FLATTEN(input => lad.raw_json:items) items;
-- 問題 4：處理特殊字元與保留字
-- 場景：JSON 鍵名包含空格或特殊字元
WITH special_keys AS (
SELECT PARSE_JSON('{
"order id": "ORD-001",
"customer-name": "王五",
"product.name": "商品A"
}') AS data
)
SELECT
-- 使用括號標記法存取特殊鍵名
data['order id']::STRING AS order_id,
data['customer-name']::STRING AS customer_name,
data['product.name']::STRING AS product_name
FROM special_keys;
-- 問題 5：除錯複雜的 JSON 查詢
-- 使用階段性輸出來追蹤資料轉換
WITH step1 AS (
SELECT
id,
raw_json,
raw_json:items AS items_raw
FROM json_data_example
),
step2 AS (
SELECT
id,
items_raw,
ARRAY_SIZE(items_raw) AS item_count
FROM step1
),
step3 AS (
SELECT
s.id,
items.value AS item_detail,
items.index AS item_index
FROM step2 s,
LATERAL FLATTEN(input => s.items_raw) items
)
SELECT * FROM step3;
-- 問題 6：處理 JSON 資料的編碼問題
-- 場景：JSON 中包含非 UTF-8 字元
SELECT
id,
raw_json:customer.name::STRING AS name,
-- 清理非法字元
REGEXP_REPLACE(
raw_json:customer.name::STRING,
'[^\\x20-\\x7E\\x{4E00}-\\x{9FFF}]',
''
) AS cleaned_name
FROM json_data_example;
-- 問題 7：監控與告警
-- 建立監控查詢以偵測資料品質問題
CREATE OR REPLACE VIEW data_quality_alerts AS
SELECT
'Missing customer name' AS alert_type,
COUNT(*) AS record_count
FROM json_data_example
WHERE raw_json:customer.name IS NULL
UNION ALL
SELECT
'Invalid payment amount' AS alert_type,
COUNT(*) AS record_count
FROM json_data_example
WHERE TRY_TO_NUMBER(raw_json:payment.amount) IS NULL
AND raw_json:payment.amount IS NOT NULL
UNION ALL
SELECT
'Empty items array' AS alert_type,
COUNT(*) AS record_count
FROM json_data_example
WHERE ARRAY_SIZE(raw_json:items) = 0;
-- 查看告警
SELECT * FROM data_quality_alerts
WHERE record_count > 0;
這些疑難排解技巧在實務中非常實用。型別轉換問題是最常見的陷阱，使用容錯函數能夠大幅提升系統的穩健性。資料品質監控則讓我們能夠及早發現問題，而不是等到業務報表出現異常才發現。在台灣某零售客戶的專案中，我們建立了完整的資料品質監控體系，每日自動檢查數十項指標，這讓資料問題的發現時間從數天縮短至數小時。
結語：掌握 JSON 資料處理的關鍵技能
Snowflake 的 JSON 查詢能力為現代資料工程提供了強大工具。透過 PARSE_JSON、FLATTEN、路徑表達式等核心功能，我們能夠優雅地處理各種複雜的半結構化資料。本文從基礎概念到進階技巧，系統性地介紹了 Snowflake JSON 查詢的完整知識體系。
關鍵要點回顧：首先是理解 VARIANT 型別的儲存機制，這是高效查詢的基礎。其次是掌握路徑表達式與 FLATTEN 函數的使用，這讓我們能夠精確提取所需資料。再者是效能最佳化策略，包括選擇性物化、叢集鍵設定、查詢邏輯重構等。最後是建立完整的資料處理流程，從原始資料載入到最終分析報表。
對於正在學習 Snowflake 的台灣讀者，筆者建議從簡單的查詢開始練習，逐步深入複雜場景。在實務專案中，優先考慮可維護性與可擴展性，而非過度最佳化。建立完善的資料品質監控機制，及早發現並解決問題。持續學習 Snowflake 的新功能，這個平台正在快速演進。
JSON 資料處理已成為現代資料工程師的必備技能。掌握 Snowflake 的 JSON 查詢能力，不僅能提升個人技術實力，更能為企業的數位轉型提供有力支撐。期望本文能為讀者在資料工程的道路上提供實用的指引，協助大家建立扎實的技術基礎。
