在查詢服務中使用巢狀資料結構
Adobe Experience Platform查詢服務支援使用巢狀資料欄位。 企業資料結構的複雜度可能會讓轉換或處理這些資料變得複雜。 本檔案提供範例，說明如何建立、處理或轉換具有複雜資料型別（包括巢狀資料結構）的資料集。
Query Service提供PostgreSQL介面，可對Experience Platform所管理的所有資料集執行SQL查詢。 Platform支援在表格欄（例如結構、陣列、對映和深度巢狀結構、陣列和對映）中使用基本或複雜的資料型別。 資料集也可以包含巢狀結構，其中的欄資料型別可以像巢狀結構的陣列一樣複雜，或是對應對應，其中索引鍵/值組的值可以是具有多個巢狀層級的結構。
快速入門
本教學課程需要使用協力廠商PSQL使用者端或查詢編輯器工具，在Experience Platform使用者介面(UI)中撰寫、驗證及執行查詢。 有關如何透過UI執行查詢的完整詳細資訊，請參閱查詢編輯器UI指南。 如需第三方案頭使用者端可連線至查詢服務的詳細清單，請參閱使用者端連線概觀。
您也應該熟悉INSERT INTO和CTAS語法。 您可以在SQL語法參考檔案的INSERT INTO和CTAS區段中，找到有關其使用方式的特定資訊。
建立資料集
查詢服務提供以選取方式建立表格(CTAS)功能，可根據SELECT陳述式的輸出來建立表格，或在此案例中，透過使用Adobe Experience Platform中現有XDM結構描述的參照來建立表格。 以下顯示的是為此範例建立的Final_subscription的XDM結構描述。
下列範例示範用來建立final_subscription_test2資料集的SQL。 final_subscription_test2是使用Final_subscription結構描述所建立。 資料是使用SELECT子句從來源擷取，以填入某些資料列。
CREATE TABLE final_subscription_test2 with(schema='Final_subscription') AS (
SELECT struct(userid, collect_set(subscription) AS subscription) AS _lumaservices3 FROM(
SELECT user AS userid,
struct( last(eventtime) AS last_eventtime,
last(status) AS last_status,
offer_id,
subsid AS subscription_id)
AS subscription
FROM (
SELECT _lumaservices3.msftidentities.userid user
, _lumaservices3.subscription.subscription_id subsid
, _lumaservices3.subscription.subscription_status status
, _lumaservices3.subscription.offer_id offer_id
, TIMESTAMP eventtime
FROM
xbox_subscription_event
UNION
SELECT _lumaservices3.msftidentities.userid user
, _lumaservices3.subscription.subscription_id subsid
, _lumaservices3.subscription.subscription_status status
, _lumaservices3.subscription.offer_id offer_id
, TIMESTAMP eventtime
FROM
office365_subscription_event
)
GROUP BY user,subsid,offer_id
ORDER BY user ASC
) GROUP BY userid)
在初始資料集final_subscription_test2中，結構資料型別用於包含subscription欄位和每個使用者唯一的userid。 subscription欄位說明使用者的產品訂閱。 可能有多個訂閱，但表格只能包含每列一個訂閱的資訊。
使用INSERT INTO來更新巢狀資料欄位
建立final_subscription_test2資料集後，INSERT INTO陳述式可用來將其他資料附加至資料表。 複製資料時，來源和目標中的資料型別必須相符。 或者，來源資料型別必須是目標資料型別的CAST。 然後使用以下SQL將增量資料新增到目標資料集中。
INSERT INTO final_subscription_test
SELECT struct(userid, collect_set(subscription) AS subscription) AS _lumaservices3 FROM(
SELECT user AS userid,
struct( last(eventtime) AS last_eventtime,
last(status) AS last_status,
offer_id,
subsid AS subscription_id)
AS subscription
FROM SELECT _lumaservices3.msftidentities.userid user
, _lumaservices3.subscription.subscription_id subsid
, _lumaservices3.subscription.subscription_status status
, _lumaservices3.subscription.offer_id offer_id
, TIMESTAMP eventtime
FROM
xbox_subscription_event
UNION
SELECT _lumaservices3.msftidentities.userid user
, _lumaservices3.subscription.subscription_id subsid
, _lumaservices3.subscription.subscription_status status
, _lumaservices3.subscription.offer_id offer_id
, timestamp eventtime
FROM
office365_subscription_event
)
GROUP BY user,subsid,offer_id
ORDER BY user ASC
) GROUP BY userid)
處理巢狀資料集中的資料
若要從資料集中找出使用者的作用中訂閱清單，您必須編寫查詢，將陣列元素分隔成多個列和欄。 若要這麼做，您必須先瞭解資料模型的形狀，因為訂閱資訊會保留在資料集中的巢狀陣列內。
PSQL \d命令是用來逐層瀏覽至必要的訂閱資料。 這些表格說明final_subscription_test2資料集的結構。 複雜的資料型別不是典型的型別值（例如文字、布林值、時間戳記等），因此一眼就能辨識。
_lumaservices3下一欄的欄位會使用\d final_subscription_test2__lumaservices3命令顯示。
useridsubscriptionsubscription是結構元素的陣列。 其欄位會使用\d _lumaservices3_subscription_e[]命令顯示。
last_eventtimelast_statusoffer_idsubscription_id若要查詢訂閱的巢狀欄位，您必須先將subscription陣列的元素分隔成多列，然後使用explode函式傳回結果。 下列SQL範例會根據userid傳回使用者的使用中訂閱。
SELECT userid, subs AS active_subscription FROM (
SELECT _lumaservices3.userid AS userid, explode(_lumaservices3.subscription) AS subs
FROM final_subscription_test2
)
WHERE subs.last_status='Active';
此簡化的範例解決方案僅允許一位使用中的使用者訂閱。 實際上，單一使用者可以有許多作用中的訂閱。 下列範例將先前的查詢修改為允許多個同時作用中的訂閱。
SELECT userid, collect_list(subs) AS active_subscriptions FROM (
SELECT
_lumaservices3.userid AS userid,
explode(_lumaservices3.subscription) AS subs
FROM final_subscription_test2
)
WHERE subs.last_status='Active'
GROUP BY userid ;
儘管此SQL範例的複雜性日益增加，但作用中訂閱的collect_list無法保證輸出會與來源保持相同的順序。 若要為使用者建立作用中訂閱清單，您必須使用GROUP BY或隨機排列來彙總清單的結果。
後續步驟
閱讀本檔案後，您現在瞭解如何在Adobe Experience Platform查詢服務中處理或轉換使用複雜資料型別的資料集。 請參閱查詢執行指南，以取得在Data Lake中的資料集上執行SQL查詢的詳細資訊。