I am attempting to aggregate data into a structure similar to
-- Starting CustomerId INTEGER, ContactId STRING, Name STRING, Phone STRING -- Desired Result CustomerId INTEGER, Contacts ARRAY< STRUCT< ContactId STRING, Name STRING, Phone STRING > >
GROUP BY I am left with a struct of
sort_col, col0, col1, etc., so I am trying to
TRANSFORM that result, but it is unable to find the columns defined by
CREATE TABLE TEMP_CONTACTS AS SELECT CustomerId, TOPK( ContactId, Name, Phone, 10) AS Contacts FROM CONTACTS GROUP BY CustomerId EMIT CHANGES; SELECT CustomerId, TRANSFORM(Contacts, (contact) => STRUCT(ContactId := contact->sort_col)) AS Contacts FROM TEMP_CONTACTS EMIT CHANGES; -- > Could not find field 'SORT_COL' in 'CONTACT'.
Is there a better way to accomplish this aggregation, or something I am missing with the transformation? I had tried to convert it to an single
STRUCT and using other aggregate functions on that, but the other aggregate functions did not accept a