TSQL FOR JSON nested value

Question

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.

Only with some string manipulation or even worse some dynamic SQL, That said, it seems like an odd structure to me ... Question: how would multiple records be presented — John Cappelletti, Jun 21 '22 at 17:23
@JohnCappelletti I only have a single record example from the customer - who wants data back. Conceivably, I could send single records anyway - sort of individual order line updates. — Anthony Horne, Jun 21 '22 at 17:48

John Cappelletti · Accepted Answer · 2022-06-21T18:22:07.523

1

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

edited Jun 21 '22 at 18:22

answered Jun 21 '22 at 18:12

John Cappelletti

79,615
7
44
66

1

Don't forget the `STRING_ESCAPE` when cobbling together JSON like this. Today it's Bob, tomorrow it's Newline Bob. (The [docs](https://learn.microsoft.com/sql/t-sql/functions/string-escape-transact-sql#b-format-json-object) actually have an interesting example of using `FORMATMESSAGE` for these purposes.) – Jeroen Mostert Jun 21 '22 at 18:18
@JeroenMostert Valid point. Updated and added the stuff/XML approach as well – John Cappelletti Jun 21 '22 at 18:24
1

You read my mind., I was busing converting and testing on STUFF.. Some docs suggest STRING_AGG is 2016, but it isn't. See: https://stackoverflow.com/a/40923548/1662973 – Anthony Horne Jun 21 '22 at 18:35

score 1 · Answer 2 · answered Jun 21 '22 at 22:03

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;

This also really helped me! Combined with https://database.guide/sql-server-for-json-path-examples-t-sql/ to get the hierarchy. — Anthony Horne, Jun 22 '22 at 10:28

TSQL FOR JSON nested value

2 Answers2