sql - 如何将多个列聚合到 HIVE 中每个组的 JSON 文件中

Question

我想将下表转换为包含两行的新表。在新表中，每个 user_id 都有一个 JSON 文件来汇总第一个表中的所有其他列。


user_id reservation_id 住宿价格
AAA 10001 1 100
美国汽车协会 10002 1 120
BBB 20003 7 350
到
user_id reservation_details
AAA {10001:{'nights':1, 'price':100}, 10002:{'nights':1, 'price':120}}
BBB {20003:{'nights':7, 'price':350}}


Answer 1

select      user_id
           ,concat("{",concat_ws(',',collect_list(concat(string(reservation_id),":{'nights':",string(nights),",'price':",string(price),"}"))),"}")  as reservation_details

from        mytable

group by    user_id       

---

+---------+-----------------------------------------------------------------+
| user_id |                       reservation_details                       |
+---------+-----------------------------------------------------------------+
| AAA     | {10001:{'nights':1,'price':100},10002:{'nights':1,'price':120}} |
| BBB     | {20003:{'nights':7,'price':350}}                                |
+---------+-----------------------------------------------------------------+