如何从Presto视图生成指定嵌套JSON并解决SQL语法报错？

问题场景 #

从Presto视图metrics_v生成指定格式的嵌套JSON时，执行的SQL报错，无法得到预期结果。

原查询语句 #

SELECT CAST(CAST(ROW('Sales (PTD)' as Ttl,unsale as Val) AS ROW(v1 VARCHAR, v2 VARCHAR)) AS JSON)
from metrics_v where time_frame='PTD';

报错信息 #

SQL Error [1]: Query failed (#20220725_063102_03638_j2tav): line 1:36: mismatched input 'as'. Expecting: ')', ','

预期JSON格式 #

"SlsPTD": {
    "Ttl": "Sales (PTD)",
    "Val": "103.27290344238281"
  }

错误原因 #

原查询的ROW构造语法不符合Presto规范——Presto中不能在ROW()内部用as指定字段名，必须先定义行类型的字段结构，再传入对应值。

解决方法 #

方法一：修正行类型构造语法 #

先定义带命名字段的行类型，再构造行对象转JSON，如需外层SlsPTD键可额外包装：

-- 生成包含Ttl和Val的JSON对象
SELECT CAST(
    ROW('Sales (PTD)', unsale) AS ROW(Ttl VARCHAR, Val VARCHAR)
) AS JSON
FROM metrics_v 
WHERE time_frame='PTD';

-- 生成带外层SlsPTD键的嵌套JSON
SELECT CAST(
    MAP(ARRAY['SlsPTD'], ARRAY[CAST(ROW('Sales (PTD)', unsale) AS ROW(Ttl VARCHAR, Val VARCHAR))]) AS JSON
)
FROM metrics_v 
WHERE time_frame='PTD';

方法二：使用json_object函数（推荐） #

Presto的json_object函数可以直接构造嵌套JSON，语法更直观，无需多次类型转换：

SELECT JSON_OBJECT(
    'SlsPTD' VALUE JSON_OBJECT(
        'Ttl' VALUE 'Sales (PTD)',
        'Val' VALUE CAST(unsale AS VARCHAR)
    )
)
FROM metrics_v 
WHERE time_frame='PTD';

内容的提问来源于stack exchange，提问作者Sormita Chakraborty