SQL Server FOR JSON生成带点号属性名JSON的解决方案

问题说明 #

需要生成符合OData语法要求的JSON结构，部分属性名本身包含点号（如spark.master、spark.databricks.cluster.profile），点号是属性名的组成部分，不能被解析为JSON嵌套层级分隔符。
直接使用带点号的列别名配合FOR JSON PATH生成时，所有点号都会被默认识别为层级分隔符，生成多余的嵌套结构，不符合预期。

实现方法 #

两种可直接落地的写法：

• 方法1：转义别名中的点号（适合结构简单的场景）
  在FOR JSON PATH的列别名中，对需要作为属性名字面量的点号，在前面加反斜杠\转义，被转义的点号不会被识别为层级分隔符。
  示例代码：

  DECLARE @id VARCHAR(10) = 'A1'
  DECLARE @sparkMaster VARCHAR(20) = 'local[0]'
  DECLARE @clusterProfile VARCHAR(20) = 'singleNode'
  
  SELECT 
      @id AS Id,
      @sparkMaster AS [new_cluster.spark_conf.spark\.master],
      @clusterProfile AS [new_cluster.spark_conf.spark\.databricks\.cluster\.profile]
  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  

• 方法2：使用JSON_MODIFY逐段构造（适合结构复杂、易写错转义符的场景，稳定性更高）
  先构造外层基础结构，再通过JSON_MODIFY逐层追加内容，在JSON路径中把带点号的键名用双引号包裹，即可将点号识别为键名的一部分，不会解析为层级。
  示例代码：

  DECLARE @id VARCHAR(10) = 'A1'
  DECLARE @sparkMaster VARCHAR(20) = 'local[0]'
  DECLARE @clusterProfile VARCHAR(20) = 'singleNode'
  DECLARE @result NVARCHAR(MAX)
  
  -- 构造最外层基础结构
  SET @result = (SELECT @id AS Id FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
  -- 初始化new_cluster下的spark_conf空对象，用JSON_QUERY避免空对象被转义为字符串
  SET @result = JSON_MODIFY(@result, '$.new_cluster.spark_conf', JSON_QUERY('{}'))
  -- 追加带点号的属性，键名用双引号包裹
  SET @result = JSON_MODIFY(@result, '$.new_cluster.spark_conf."spark.master"', @sparkMaster)
  SET @result = JSON_MODIFY(@result, '$.new_cluster.spark_conf."spark.databricks.cluster.profile"', @clusterProfile)
  
  SELECT @result
  

注意：使用JSON_MODIFY写入JSON类型的值（对象、数组）时，必须套一层JSON_QUERY，否则JSON内容会被转义为普通字符串。

两种写法最终输出的JSON结构完全符合需求：

{
  "Id" : "A1",
  "new_cluster": {
      "spark_conf":{
         "spark.master":"local[0]",
         "spark.databricks.cluster.profile": "singleNode"
       }
  }
}

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