Spark SQL query to add string column to an array of structure

Question

I am working on Query Service in Adobe Experience Platform. It uses limited Spark SQL functions which are listed here.

I have the following table

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,}]

Here Name and AddressType are String where as CustomerDetails has the schema type array<struct<acctType:string, acctID:string>>

I need to add AddressType column to the CustomerDetails column and the final output should look like the below format

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}]

I am using the below query to add an extra field to the CustomerDetails column. But I am not able to figure out how to add the value of AddressType column to col3

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

I have also looked at TRANSFORM function but not getting the SQL specific syntax to use it. Any help on this would be appreciated.

Vivek Kumar Singh · Accepted Answer · 2024-04-21 17:47:15Z

1

After digging around for a bit, I was finally able to use this answer as reference and come up with a solution. Below is the query which worked for me.

SELECT ct1.Name, ct1.addressType, to_json(ct1.col2) AS CustomerDetails
FROM
(SELECT ct.Name, ct.addressType,
TRANSFORM(ct.col3, x -> struct(ct.addressType as addressType, x.acctType as acctType, x.acctID as acctID)) as col2
FROM
(SELECT Name, addressType,
(from_json(CustomerDetails, 'ARRAY<STRUCT<addressType: STRING, acctType: STRING, acctID: STRING>>')) AS col3
FROM CustomerTable) ct) ct1

answered Apr 21, 2024 at 17:47

Vivek Kumar Singh

3,3601 gold badge19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

s.polam · Accepted Answer · 2024-04-22 08:50:08Z

1

Another alternative is to use map rather than struct since you need to specify all columns.

SELECT  
    name,
    addresstype,
    to_json(
        transform(
            from_json(
                customerdetails, 'array<map<string, string>>'
            ), 
            f -> map_concat(
                f, 
                map('AddressType', addresstype)
            )
        )
    ) AS customerdetails
FROM VALUES 
   ("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}]') 
AS (name, addresstype, customerdetails)

+----+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|name|addresstype|customerdetails                                                                                                                                                                                                 |
+----+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|John|home       |[{"acctType":"Mortgage loan","acctID":"101","AddressType":"home"},{"acctType":"Home Equity loan","acctID":"104","AddressType":"home"},{"acctType":"Checking Account","acctID":"105","AddressType":"home"}]      |
|John|work       |[{"acctType":"Mortgage loan","acctID":"101","AddressType":"work"},{"acctType":"Home Equity loan","acctID":"104","AddressType":"work"},{"acctType":"Checking Account","acctID":"105","AddressType":"work"}]      |
|John|office     |[{"acctType":"Mortgage loan","acctID":"101","AddressType":"office"},{"acctType":"Home Equity loan","acctID":"104","AddressType":"office"},{"acctType":"Checking Account","acctID":"105","AddressType":"office"}]|
+----+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

answered Apr 22, 2024 at 8:50

s.polam

10.4k2 gold badges17 silver badges29 bronze badges

5 Comments

Vivek Kumar Singh Over a year ago

Your query is throwing me an error at line 17:11: extraneous input ','. May be AEP is not liking the way data is being provided.

s.polam Over a year ago

what is AEP means ?

s.polam Over a year ago

how are you executing above query ?

Vivek Kumar Singh Over a year ago

I am using Adobe Experience Platform's Query Service feature to do so. It has very limited Spark functionalities. But I am getting the gist of what you are trying to accomplish. The usage of maps would be beneficial if I have a very large number of columns.

s.polam Over a year ago

Yes, That's correct.

Collectives™ on Stack Overflow

Spark SQL query to add string column to an array of structure

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related