如何用SQL Server内置JSON方法实现EAV数据动态生成JSON

需求说明 #

现有类EAV结构的dbo.Vehicle表，需生成指定层级结构的JSON，要求每个EAV属性作为JSON对象的独立键，且后续新增属性时无需手动修改代码即可自动适配。

示例数据结构 #

CREATE TABLE dbo.Vehicle (CustomerID int NOT NULL,
                          VehicleID int NOT NULL,
                          ColumnName varchar(30),
                          ColumnValue varchar(100));

INSERT INTO dbo.Vehicle (CustomerID,
                         VehicleID,
                         ColumnName,
                         ColumnValue)
VALUES(1,1,'Make','Ford'),
      (1,1,'Model','Focus'),
      (1,1,'Colour','Blue'),
      (1,2,'Make','Ford'),
      (1,2,'Model','Fiesta'),
      (1,2,'Colour','Black'),
      (2,1,'Make','Honda'),
      (2,1,'Model','Jazz'),
      (2,1,'Colour','Grey'),
      (2,1,'Transmission','Automatic');

目标JSON格式 #

[
    {
        "CustomerID": 1,
        "Vehicles": [
            {
                "VehicleID": 1,
                "Make": "Ford",
                "Model": "Focus",
                "Colour": "Blue"
            },
            {
                "VehicleID": 2,
                "Make": "Ford",
                "Model": "Fiesta",
                "Colour": "Black"
            }
        ]
    },
    {
        "CustomerID": 2,
        "Vehicles": [
            {
                "VehicleID": 1,
                "Make": "Honda",
                "Model": "Jazz",
                "Colour": "Grey",
                "Transmission": "Automatic"
            }
        ]
    }
]

现有方案的不足 #

• 静态方案需手动枚举所有属性，新增属性时必须修改代码。
• 字符串拼接方案（STRING_AGG）繁琐，维护性差，且需手动处理JSON转义，容易出错。

基于SQL Server内置JSON函数的动态方案 #

方案一：SQL Server 2022+（推荐） #

利用SQL Server 2022新增的JSON_OBJECT_AGG和JSON_ARRAYAGG聚合函数，直接动态生成键值对和数组，无需手动维护属性列表：

SELECT
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'CustomerID': CustomerID,
      'Vehicles': Vehicles
    ) ORDER BY CustomerID
  ) AS FinalJson
FROM (
  SELECT
    CustomerID,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'VehicleID': VehicleID,
        -- 动态聚合当前Vehicle的所有EAV属性为键值对
        (SELECT JSON_OBJECT_AGG(ColumnName, ColumnValue) 
         FROM dbo.Vehicle v2 
         WHERE v2.CustomerID = v1.CustomerID AND v2.VehicleID = v1.VehicleID)
      ) ORDER BY VehicleID
    ) AS Vehicles
  FROM dbo.Vehicle v1
  GROUP BY CustomerID
) cust;

方案优势

• 完全动态：新增ColumnName时自动纳入JSON，无需修改代码。
• 自动转义：JSON_OBJECT_AGG会自动处理JSON特殊字符的转义，避免手动调用STRING_ESCAPE。
• 代码简洁：利用内置JSON函数替代字符串拼接，可读性和维护性大幅提升。

方案二：SQL Server 2016-2019（兼容低版本） #

通过动态SQL自动生成静态转换逻辑，结合FOR JSON PATH实现动态适配：

DECLARE @cols NVARCHAR(MAX);

-- 动态获取所有唯一的ColumnName
SELECT @cols = STRING_AGG(
    'MAX(CASE WHEN V.ColumnName = ''' + ColumnName + ''' THEN V.ColumnValue END) AS ' + QUOTENAME(ColumnName),
    ','
)
FROM (SELECT DISTINCT ColumnName FROM dbo.Vehicle) t;

-- 构造并执行动态SQL
DECLARE @sql NVARCHAR(MAX) = N'
SELECT C.CustomerID,
       (SELECT V.VehicleID, ' + @cols + '
        FROM dbo.Vehicle V
        WHERE V.CustomerID = C.CustomerID
        GROUP BY V.VehicleID
        FOR JSON PATH) AS Vehicles
FROM dbo.Vehicle C
GROUP BY C.CustomerID
FOR JSON PATH;
';

EXEC sp_executesql @sql;

方案优势

• 兼容SQL Server 2016及以上版本（FOR JSON PATH从2016开始支持）。
• 自动适配新增属性，无需手动修改核心逻辑。
• 利用FOR JSON PATH自动处理JSON格式和转义，比字符串拼接更可靠。

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