在查询服务中使用嵌套数据结构
Adobe Experience Platform查询服务支持使用嵌套数据字段。 企业数据结构的复杂性会使这些数据的转换和处理变得复杂。 本文档提供了如何使用复杂数据类型（包括嵌套数据结构）创建、处理或转换数据集的示例。
查询服务提供了一个PostgreSQL接口，用于对Experience Platform管理的所有数据集运行SQL查询。 Platform支持在表列（如struct 、 arrays 、 maps和深度嵌套的struct 、 arrays和maps ）中使用原始或复杂数据类型。 数据集还可以包含嵌套结构，其中列数据类型可以像嵌套结构的数组一样复杂，或者包含映射映射，其中键值对的值可以是具有多个嵌套级别的结构。
快速入门
本教程需要使用第三方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查询服务中处理或转换使用复杂数据类型的数据集。 有关在数据湖中的数据集上运行SQL查询的详细信息，请参阅查询执行指南。