合并分段JSON消息并拆分请求/响应/状态码列的SQL需求

一、支持大消息合并的方案 #

方案1：优化STRING_AGG用法（SQL Server 2017及以上） #

STRING_AGG默认返回类型取决于输入列，针对大消息需确保聚合结果显式指定为NVARCHAR(MAX)，避免内容截断。以下是按Ops合并Segment>1消息的实现：

WITH MergedCTE AS (
    SELECT
        Ops,
        Segment,
        Message,
        -- 合并同Ops下所有Segment>1的消息，按Segment顺序拼接
        STRING_AGG(CASE WHEN Segment > 1 THEN Message END, '') WITHIN GROUP (ORDER BY Segment) OVER (PARTITION BY Ops) AS MergedMsg
    FROM YourTableName
)
SELECT
    DISTINCT Ops,
    -- Segment=1保留原消息，否则用合并后的内容
    CASE WHEN Segment = 1 THEN Message ELSE MergedMsg END AS FinalMessage,
    -- 拆分Request字段，兼容GET/POST不同JSON结构
    CASE
        -- 假设GET请求的Request为字符串格式（如"GET /api/xxx"）
        WHEN JSON_VALUE(CASE WHEN Segment=1 THEN Message ELSE MergedMsg END, '$.Request') LIKE 'GET%'
        THEN JSON_VALUE(CASE WHEN Segment=1 THEN Message ELSE MergedMsg END, '$.Request')
        -- POST请求的Request为JSON对象（如{"Method":"POST","Body":"..."}）
        ELSE JSON_QUERY(CASE WHEN Segment=1 THEN Message ELSE MergedMsg END, '$.Request')
    END AS Request,
    -- 提取Response和StatusCode
    JSON_VALUE(CASE WHEN Segment=1 THEN Message ELSE MergedMsg END, '$.Response') AS Response,
    JSON_VALUE(CASE WHEN Segment=1 THEN Message ELSE MergedMsg END, '$.StatusCode') AS StatusCode
FROM MergedCTE

方案2：FOR XML PATH 正确实现（兼容低版本SQL Server） #

如果使用SQL Server 2016及以下，可通过FOR XML PATH结合TYPE节点避免截断和特殊字符转义，确保完整捕获大消息：

WITH MergedCTE AS (
    SELECT
        t1.Ops,
        t1.Segment,
        t1.Message,
        -- 合并同Ops下Segment>1的消息，按顺序拼接
        STUFF((
            SELECT '' + t2.Message
            FROM YourTableName t2
            WHERE t2.Ops = t1.Ops AND t2.Segment > 1
            ORDER BY t2.Segment
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 0, '') AS MergedMsg
    FROM YourTableName t1
)
SELECT
    Ops,
    CASE WHEN Segment = 1 THEN Message ELSE MergedMsg END AS FinalMessage,
    -- 拆分字段逻辑同方案1
    CASE
        WHEN JSON_VALUE(CASE WHEN Segment=1 THEN Message ELSE MergedMsg END, '$.Request') LIKE 'GET%'
        THEN JSON_VALUE(CASE WHEN Segment=1 THEN Message ELSE MergedMsg END, '$.Request')
        ELSE JSON_QUERY(CASE WHEN Segment=1 THEN Message ELSE MergedMsg END, '$.Request')
    END AS Request,
    JSON_VALUE(CASE WHEN Segment=1 THEN Message ELSE MergedMsg END, '$.Response') AS Response,
    JSON_VALUE(CASE WHEN Segment=1 THEN Message ELSE MergedMsg END, '$.StatusCode') AS StatusCode
FROM MergedCTE
GROUP BY Ops, Segment, Message, MergedMsg

二、无需合并的拆分方案 #

如果业务允许保留分段数据，可直接对每条记录的JSON进行拆分，实现更简单：

SELECT
    GUID,
    Ops,
    Segment,
    -- 兼容GET/POST的Request提取
    CASE
        WHEN JSON_VALUE(Message, '$.Request') LIKE 'GET%'
        THEN JSON_VALUE(Message, '$.Request')
        ELSE JSON_QUERY(Message, '$.Request')
    END AS Request,
    JSON_VALUE(Message, '$.Response') AS Response,
    JSON_VALUE(Message, '$.StatusCode') AS StatusCode
FROM YourTableName

注意事项 #

• 确保所有Message列的JSON格式合法，否则JSON_VALUE/JSON_QUERY会返回NULL，可通过ISNULL添加默认值处理
• 若GET/POST的JSON结构差异较大，需调整CASE判断逻辑（比如根据是否存在Response字段区分）
• 合并操作必须按Segment顺序排序，否则拼接后的JSON会损坏

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