我试图通过tsql层次 struct 表输出JSON.
代码:
select Field1, Field2 from @header inner join @line on @header.Id = @Line.Id FOR JSON AUTO
我得到:
"Field1": "BOB",
"Field2": "BOB2",
但我希望它能展示出来
"Field1": {
"value": "BOB"
},
"Field2": {
"value": "BOB2"
},
我做错了什么？我可以使用文本操作，但我想知道是否有一种"幸运的"方式，即内置的可读性和最佳实践.
Select STRING_AGG(concat('"',[Key],'":{"value":"',string_escape(Value,'json'),'"}'),',')
From OpenJson( (Select * From @YourTable For JSON Path,Without_Array_Wrapper ) )
For 2016 -- STUFF XML
Select stuff((Select concat('"',[Key],'":{"value":"',string_escape(Value,'json'),'"}')
From OpenJson( (Select * From @YourTable For JSON Path,Without_Array_Wrapper ) )
For XML Path ('')),1,0,'')
Results
"Field1":{
"value":"Bob"
},
"Field2":{
"value":"Bob2"
}