SQL Server中如何给列追加逗号且排除最后一行以导出合法JSON

首先假设存在测试表，执行查询：

select * from myTable

返回结果如下：

需求是将表数据导出为每行一个JSON对象的数组格式，适配500万条大表导出场景，不能使用list_agg这类将所有内容拼接为单行字符串的函数。原有实现脚本如下：

DECLARE @json NVARCHAR(Max)
SET @json = (SELECT * FROM myTable FOR JSON PATH, ROOT('data'))
SELECT '[' UNION ALL

SELECT value+','
FROM OPENJSON(@json,'$.data') UNION ALL

SELECT ']'

该脚本导出的结果最后一个JSON对象末尾多了逗号，导致JSON格式不合法，大文件无法手动编辑修正，需要在查询层面直接解决问题。

可以借助窗口函数标记行号和总记录数，判断是否为最后一行来决定是否追加逗号，修改后的脚本如下：

DECLARE @json NVARCHAR(Max)
SET @json = (SELECT * FROM myTable FOR JSON PATH, ROOT('data'))

SELECT '[' AS output_line
UNION ALL
SELECT 
    value + CASE WHEN row_num = total_count THEN '' ELSE ',' END AS output_line
FROM (
    SELECT 
        value,
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS row_num,
        COUNT(*) OVER() AS total_count
    FROM OPENJSON(@json,'$.data')
) AS json_lines
UNION ALL
SELECT ']' AS output_line

方案优势 #

• 完美解决最后一行多余逗号问题，导出的内容直接是合法JSON格式，无需后续手动处理
• 每条JSON对象依然单独占一行，无单行全量拼接操作，完全适配百万级大表导出需求
• 不修改原有JSON对象的内容结构，兼容性高

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