json - 在 SQL 中使用包含点的属性名称生成 JSON

Question

我需要从 SQL Server 中的表生成以下 JSON 负载(缩写)。请注意属性名称中的点。这是一种称为 OData 的特殊语法。

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

我尝试了以下 T-SQL 命令:

SELECT @id as ID, @name1 AS [new_cluster.spark_conf.spark.master]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

这显然会导致:

{
  "Id" : "A1",
  "new_cluster": {
    "spark_conf": {
      "spark": {
         "master":  "local[0]"
       }
    }
  }
}

我已经彻底阅读了有关 SQL Server 中 JSON 功能的完整文档，文档中没有描述在属性名称中转义点的地方。

Answer 1

我不确定直接使用 FOR JSON PATH 是否是一个可行的解决方案。文档解释说，... FOR JSON PATH 子句使用列别名或列名来确定 JSON 输出中的键名。如果别名包含点，则 PATH 选项会创建嵌套对象。 ...，所以这种行为是意料之中的。在这种情况下可能的解决方法是:

使用 JSON_MODIFY():

DECLARE @id varchar(2) = 'A1'
DECLARE @name1 varchar(10) = 'local[0]'
SELECT 
   @id as ID, 
   JSON_MODIFY('{}', '$."spark.master"', @name1) AS [new_cluster.spark_conf]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

FOR JSON PATH 和FOR JSON AUTO 的组合:

DECLARE @id varchar(2) = 'A1'
DECLARE @name1 varchar(10) = 'local[0]'
SELECT 
   @id as ID, 
   JSON_QUERY((
      SELECT @name1 AS [spark.master]
      FROM (VALUES (NULL)) v (v)
      FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
   )) AS [new_cluster.spark_conf]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

结果:

{"ID":"A1","new_cluster":{"spark_conf":{"spark.master":"local[0]"}}}

有关 SQL Server 2022 JSON 新功能的说明。如果我可以信任文档，新引入的 JSON_OBJECT()函数将(可能)提供另一种选择:

SELECT JSON_OBJECT(
   'Id': @id,
   'new_cluster': JSON_OBJECT('spark_conf': JSON_OBJECT('spark.master': @name1))
)