Databricks SQL查询输出含转义字符，如何修改生成标准JSON

问题根源在于你在子查询nested_json里提前把ChargesDetail转成了JSON字符串，外层主查询再次执行TO_JSON时，会把这个字符串当作普通文本进行转义，导致出现\和额外引号。

直接修改子查询，去掉多余的TO_JSON，保留数组的原生结构即可：

SELECT 
    orders.GroceryStore,
    TO_JSON(COLLECT_LIST(MAP(
        'CustomerID', orders.CustomerID,
        'DiscountValue', orders.DiscountValue,
        'SalesAmount', orders.SalesAmount,
        'ChargesInfo', nested_json.ChargeDetails
    ))) AS JsonLine
FROM (
    SELECT h.GroceryStore, d.CustomerID, SUM(d.DiscountValue) AS DiscountValue, SUM(d.SalesAmount) AS SalesAmount
    FROM SalesHeader h
    LEFT JOIN SalesDetail d ON h.CustomerID = d.CustomerID
    WHERE h.Date >= '2024-01-01'
    GROUP BY h.GroceryStore, d.CustomerID
) AS orders
LEFT JOIN (
    SELECT 
        d.CustomerID,
        -- 移除这里的TO_JSON，直接返回数组结构
        COLLECT_LIST(MAP(
            'ChargeType', d.ChargeType, 
            'ChargeAmount', d.ChargeAmount,
            'PaidAmount', d.PaidAmount
        )) AS ChargeDetails
    FROM ChargesDetail d
    GROUP BY d.CustomerID
) AS nested_json ON orders.CustomerID = nested_json.CustomerID
GROUP BY orders.GroceryStore;

关键改动说明 #

• 子查询nested_json中不再将收集到的Charge数组转成JSON字符串，而是保留Spark原生的数组-映射结构
• 主查询的TO_JSON会自动识别这个结构，将其序列化为原生JSON数组，不会产生转义字符或额外引号，最终输出符合你期望的标准JSON格式。

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