TSQL FOR JSON nested value-CodePudding

I am trying to output JSON via tsql hierarchy table.

Code:

select Field1, Field2 from @header inner join @line on @header.Id = @Line.Id FOR JSON AUTO

I am getting:

"Field1": "BOB",
"Field2": "BOB2",

but I am looking for it to display

 "Field1": {
   "value": "BOB"
 },
 "Field2": {
   "value": "BOB2"
 },

What am I doing wrong? I can using text manipulation, but was wondering if there is a "blessed" way, i.e. built-in that is readable and best-practice.

CodePudding user response：

 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"
}

CodePudding user response：

You can just use a path for each column name, combined with FOR JSON PATH.

SELECT
  Field1 AS [Field1.value],
  Field2 AS [Field2.value]
FROM @header h
JOIN @line l ON h.Id = l.Id
FOR JSON PATH;

If you need the two fields as entirely separate rows each with its own object, you can unpivot it

SELECT
  v.[Field1.value],
  v.[Field2.value]
FROM @header h
JOIN @line l ON h.Id = l.Id
CROSS APPLY (VALUES
    (Field1, NULL),
    (NULL, Field2)
) v([Field1.value], [Field2.value])
FOR JSON PATH;