如何用SQL拼json+数组

问题遇到的现象和发生背景

数据样例如图，想按照poiid进行聚合，后面的数据按照json+数组的格式展示在一起成为dealList

我想要达到的结果

SQL运行结果效果是这个样子的，求推荐使用的函数
dealList: "[{"dealid":11111,"order_7d":1,"consume_7d":5},{"dealid":22222,"order_7d":2,"consume_7d":6}]"

先将后三个字段组合拼好，之后按照poiid分组聚合，相同poiid的进行汇总并通过concat_ws转为字符串，之后再拼接上头尾的字符串即可

SELECT 
    poiid,
    CONCAT('dealList:"[',str,']"') AS str
FROM(
    SELECT 
        poiid,
        CONCAT_WS(',',collect_list(str)) AS str
    FROM( 
        SELECT 
            poiid,
            CONCAT('{"dealid":',CAST(dealid AS string),',"order_7d":'CAST(order_7d AS string),',"consume_7d":',CAST(consume_7d AS string),'}') AS str
        FROM tb
        )p
    GROUP BY 
        p.poiid
    )k