I have an output table that resembles: User Preference User A Pref A User A Pref B I'd like to get the data into the following format: User Preferences, which is array of struct User A [{pref => "Pref A"}, {pref => "Pref B"} ] I attempted the following, but to no avail: SELECT User, ARRAY_AGG(SELECT AS STRUCT(Preference) ) as Preferences FROM users GROUP BY User Curious if anyone might have any pointers? Thank you in advance

How to combine multiple rows into a STRUCT in SQL from a query on Hive?

I have an output table that resembles:

User	Preference
User A	Pref A
User A	Pref B

I'd like to get the data into the following format:

User	Preferences, which is array of struct
User A	[{pref => "Pref A"}, {pref => "Pref B"} ]

I attempted the following, but to no avail:

SELECT
  User,
  ARRAY_AGG(SELECT AS STRUCT(Preference)
  ) as Preferences
FROM
  users
GROUP BY User

Curious if anyone might have any pointers? Thank you in advance