Generate JSON with property names that include dot in SQL

user_18439540 0
Viewed 112

I need to generate the following JSON payload (shortened) from a table in SQL Server. Please note the dot in the property name. This is a special syntax called OData.

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

I have tried the following T-SQL command:

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

Which obviously results to:

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

I have already read the full documentation around JSON functionality in SQL Server thoroughly and no where in the documentation escaping dot in property names has been described.

tsql sql-server json
1 Answers

I'm not sure if using FOR JSON PATH directly is a working solution. The documentation explains, that ... The FOR JSON PATH clause uses the column alias or column name to determine the key name in the JSON output. If an alias contains dots, the PATH option creates nested objects. ..., so this behaviour is expected. Possible workarounds in this situation are:

Using 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

A combination of FOR JSON PATH and 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

Result:

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

A note about the new SQL Server 2022 JSON features. If I can trust the documentation, the newly introduced JSON_OBJECT() function will (probably) provide another option:

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