如何让SQL Server查询返回纯值列表而非带键对象的JSON结果？

架构老林

2026-6-13

SQL Server生成纯数值数组的JSON输出

问题描述

现有如下SQL Server查询语句，希望生成的JSON中chartData字段是纯数值列表，而非包含mSales键的对象数组。但使用WITHOUT_ARRAY_WRAPPER关键字时，数值会被转为字符串，无法得到预期格式。

原查询语句

DECLARE @chartData TABLE
(
    mSales MONEY
)
INSERT INTO @chartData (mSales)
SELECT 498.65 AS mSales
UNION ALL
SELECT 439.83
UNION ALL
SELECT 492.23
UNION ALL
SELECT 439.40;

SELECT 
    'IPM Sales' AS chartTitle,
    (SELECT * FROM @chartData FOR JSON PATH) AS chartData
FOR JSON PATH

当前输出

[
    {
        "chartTitle": "IPM Sales",
        "chartData": [
            {"mSales": 498.6500},
            {"mSales": 439.8300},
            {"mSales": 492.2300},
            {"mSales": 439.4000}
        ]
    }
]

预期输出

[
    {
        "chartTitle": "IPM Sales",
        "chartData": [
            498.6500,
            439.8300,
            492.2300,
            439.4000
        ]
    }
]

解决方案

可以通过STRING_AGG拼接数值为JSON数组格式的字符串，再用JSON_QUERY将其解析为JSON类型，避免被转为字符串。修改后的查询语句如下：

DECLARE @chartData TABLE
(
    mSales MONEY
)
INSERT INTO @chartData (mSales)
SELECT 498.65 AS mSales
UNION ALL
SELECT 439.83
UNION ALL
SELECT 492.23
UNION ALL
SELECT 439.40;

SELECT 
    'IPM Sales' AS chartTitle,
    JSON_QUERY('[' + STRING_AGG(mSales, ',') + ']') AS chartData
FROM @chartData
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

原理说明

STRING_AGG(mSales, ',')：将所有mSales数值用逗号连接成字符串，得到"498.65,439.83,492.23,439.40"
拼接[]后形成合法的JSON数组字符串："[498.65,439.83,492.23,439.40]"
JSON_QUERY：告诉SQL Server该字符串是合法的JSON内容，不会在外层JSON中对其添加引号或转义，最终输出为纯数值数组。