How to create a single object JSON array in SQL?

Question

I am trying to convert a row of data into a JSON array containing a single object. One column contains XML, which outlines another single object that is converted to an array.

My query:

WITH r AS (
SELECT TOP 1 * FROM Table1
ORDER BY RecordID ASC)

SELECT 
NEWID() AS 'Report.ReportUUID', 
Value1 as 'Report.Value1', 
Value2 as 'Report.Value2',
DateTime as 'Report.DateTime',
UserID as 'Report.UserID',
'Medium' as 'Report.Priority',
NEWID() as 'Report.Item.ItemUUID',
XML.value('category[1]', 'varchar(100)') as 'Report.Item.Category',
XML.value('description[1]', 'varchar(1000)') as 'Report.Item.Description',
XML.value('date[1]', 'varchar(100)') AS 'Report.Item.DateTime'
FROM r
FOR JSON PATH, ROOT('DataSet');

Desired output:

{
  "DataSet" : {
    "Report" : [
      {
        "ReportUUID" : "uuid here",
        "Value1" : "value1",
        "Value2" : "value2",
        "DateTime" : "2020-04-06 16:00:00",
        "UserID" : "1234",
        "Priority" : "Medium",
        "Item" : [
          {
            "ItemUUID" : "uuid here",
            "Category" : "01",
            "Description" : "Desc",
            "DateTime" : "2020-04-05 08:00:00"
          }
        ]
      }
    ]
  }
}

Actual output:

{
    "DataSet": [
        {
            "Report": {
                "ReportUUID" : "uuid here",
                "Value1": "value1",
                "Value2": "value2",
                "DateTime": "2020-04-06 16:00:00",
                "UserID": "1234",
                "Priority": "Medium",
                "Item": {
                    "ItemUUID": "uuid here",
                    "Category": "01",
                    "Description": "Desc",
                    "DateTime": "2020-04-05 08:00:00"
                }
            }
        }
    ]
}

DataSet needs to be a single object, and Report and Item have to be arrays containing a single object. Can anyone help with formatting the data like this?

What's the SQL Server version used?

gotqn
– gotqn

2020-04-06 06:21:35 +00:00
Commented Apr 6, 2020 at 6:21 — gotqn
– gotqn, Commented Apr 6, 2020 at 6:21
Microsoft SQL Server 2017, version 13.0.5101.9

Nick
– Nick

2020-04-06 06:26:18 +00:00
Commented Apr 6, 2020 at 6:26 — Nick
– Nick, Commented Apr 6, 2020 at 6:26

Zhorov · Accepted Answer · 2020-04-06 06:45:18Z

6

You need a statement like the following:

;WITH r AS (
   SELECT TOP 1 * FROM Table1
   ORDER BY RecordID ASC
)
SELECT Report AS 'DataSet.Report'
FROM (
   SELECT 
      NEWID() AS 'ReportUUID', 
      Value1 as 'Value1', 
      Value2 as 'Value2',
      DateTime as 'DateTime',
      UserID as 'UserID',
      'Medium' as 'Priority',
      (
      SELECT 
         NEWID() as 'ItemUUID',
         XML.value('category[1]', 'varchar(100)') as 'Category',
         XML.value('description[1]', 'varchar(1000)') as 'Description',
         XML.value('date[1]', 'varchar(100)') AS 'DateTime'
      FOR JSON PATH   
      ) AS 'Item'
   FROM r
   FOR JSON AUTO
) t (Report)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

answered Apr 6, 2020 at 6:45

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simon Over a year ago

For anyone coming across this from a google search, like me, the secret sauce here was the WITHOUT_ARRAY_WRAPPER keyword

Collectives™ on Stack Overflow

How to create a single object JSON array in SQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related