t-sql for json跳过结果中的列名

Question

我有以下返回json的t-sql查询：

SELECT
    CF.Name
  , UCF.Value
FROM dbo.ClaimsCustomFields CF
LEFT JOIN dbo.UserCustomFields UCF
       ON UCF.FieldId = CF.Id
WHERE CF.CustomerId = 2653
FOR JSON PATH;

该查询的输出如下：

    [  
   {  
      "Name":"zipCode",
      "Value":"zip zip zipC  zipCod"
   },
   {  
      "Name":"time111zone",
      "Value":"UTC +2"
   },
   {  
      "Name":"tttt",
      "Value":"Company organization tessss"
   }
]

但我希望得到以下格式的结果：

   [  
   {  
      "zipCode":"zip zip zipC  zipCod"
   },
   {  
      "time111zone":"UTC +2"
   },
   {  
      "tttt":"Company organization tessss"
   }
]

有没有可能用FOR JSON语句实现这一点？

Answer 1

您基本上希望生成一个动态json，因此您可以尝试使用动态TSQL，以利用SQL Server2017新函数STRING_AGG (更多信息here)：

--this table contains your sample data
declare @tmp table([Name] varchar(50),[Value] varchar(50))
--this variable will contain the dynamic tsql command 
declare @sql nvarchar(max)

--this temp table will hold the dynamically generated json fragments
if object_id('#tmp') is null
    create table #tmp (js varchar(max))

--fill table with test data 
insert into @tmp values
    ('zipCode'     ,'zip zip zipC  zipCod'),
    ('time111zone' ,'UTC +2'),
    ('tttt'        ,'Company organization tessss')

--generate a TSQL command that inserts each single JSON fragment into a temp table
select @sql = string_agg('insert into #tmp(js) values((select ''' + [Value] +''' as ''' 
              + [Name]+'''  for json path , WITHOUT_ARRAY_WRAPPER))', ';') 
from @tmp

--execute the dynamic TSQL command that fills the temp table with JSON fragments
exec(@sql)

--concatenate all the JSON fragments adding square brackets
select '[' + string_agg(js,',') + ']' as result from #tmp  

结果：

​

​

[{"zipCode":"zip zip zipC  zipCod"},{"time111zone":"UTC +2"},{"tttt":"Company organization tessss"}]