在Adobe Experience Platform中为结构体数组添加字符串列的Spark SQL实现

我正在使用Adobe Experience Platform的Query Service，该服务仅支持有限的Spark SQL函数。现有表结构如下：

Name   AddressType    CustomerDetails
------------------------------------------------------------------------------------------      
John   home           [{"acctType":"Mortgage loan","acctID":101},{"acctType":"Home Equity loan","acctID":104},{"acctType":"Checking Account","acctID":105,}]
John   work           [{"acctType":"Mortgage loan","acctID":101},{"acctType":"Home Equity loan","acctID":104},{"acctType":"Checking Account","acctID":105,}]
John   office         [{"acctType":"Mortgage loan","acctID":101},{"acctType":"Home Equity loan","acctID":104},{"acctType":"Checking Account","acctID":105,}]

其中Name和AddressType为String类型，CustomerDetails的schema类型为array<struct<acctType:string, acctID:string>>。

我需要将AddressType列的值添加到CustomerDetails列的每个结构体中，最终输出格式如下：

Name   AddressType    CustomerDetails
--------------------------------------      
John   home           [{"AddressType":"home","acctType":"Mortgage loan","acctID":101}, {"AddressType":"home","acctType":"Home Equity loan","acctID":104}, {"AddressType":"home","acctType":"Checking Account","acctID":105}]
John   work           [{"AddressType":"work","acctType":"Mortgage loan","acctID":101}, {"AddressType":"work","acctType":"Home Equity loan","acctID":104}, {"AddressType":"work","acctType":"Checking Account","acctID":105}]
John   office         [{"AddressType":"office","acctType":"Mortgage loan","acctID":101}, {"AddressType":"office","acctType":"Home Equity loan","acctID":104}, {"AddressType":"office","acctType":"Checking Account","acctID":105}]

我尝试了以下查询，但无法将AddressType的值加入新列：

SELECT Name, addressType,
(from_json(CustomerDetails, 'ARRAY<STRUCT<addressType: STRING, acctType: STRING, acctID: STRING>>')) AS col3
FROM CustomerTable 

我了解到可以使用TRANSFORM函数，但不清楚它在Spark SQL中的正确语法，希望得到解决方案。

内容的提问来源于stack exchange，提问作者Vivek Kumar Singh