SQL Server - Generate Dynamic JSon output-CodePudding

I have the following tables (pseudo names below) in SQL which I need to use to produce some dynamic JSON out from :

HeaderTable

RequestID int
RequestType varchar(50)

Sample Data

1 : User Name Change
2 : User Name Change

ValuesTable

RequestID int
Alias varchar(50)
FieldValue varchar(50)

Sample Data

1 : MobileNo : 07777777777
1 : Name : Fred Bloggs
2 : MobileNo : 07888888888
2 : Name : John Smith

The JSON I need to end up with is as follows :

[
{
    "request_type":"User Name Change",
    "request_details":[
        {
            "MobileNo":"07777777777",
            "Name":"Fred Bloggs"
        },
        {
            "MobileNo":"07888888888",
            "Name":"John Smith"
        }
    ]
}

]

So I effectively need to pass my alias value as the key name in the JSON.

My code so far is as follows but I assume I might need some sort of dynamic SQL?

SELECT hdr.RequestType AS request_type
    , (
        SELECT vals.FieldValue  AS [request_details.value]
        FROM ValuesTable vals
        WHERE vals.RequestID = hdr.[RequestID]  
        FOR JSON PATH
      ) request_details
FROM HeaderTable hdr
FOR JSON PATH

I'm not sure if any of the other SQL JSON function might be useful here or if I need to somehow churn out some dynamic SQL as my only hope?

CodePudding user response：

I don't think you can build the required JSON directly (usinf FOR JSON), but you may try to build one part of the JSON output using basic string concatenation and aggregation. Note, that starting for SQL Server 2016, you need to use FOR XML PATH for aggregation:

Data:

SELECT *
INTO HeaderTable
FROM (VALUES
   (1, 'User Name Change')
) v (RequestID, RequestType)

SELECT *
INTO ValuesTable
FROM (VALUES
   (1, 'MobileNo', '07777777777'),
   (1, 'Name',  'Fred Bloggs'),
   (1, 'Address',  'Full address'),
   (2, 'MobileNo', '07888888888'),
   (2, 'Name', 'John Smith')
) v (RequestID, Alias, FieldValue)

Statement for SQL Server 2017:

SELECT 
   hdr.RequestType AS request_type, 
   JSON_QUERY((
      SELECT CONCAT(
         '[{',
         STRING_AGG(
            CONCAT(
               '"', 
               STRING_ESCAPE(vals.Alias, 'json'), 
               '":"', 
               STRING_ESCAPE(vals.FieldValue, 'json'), '"'
            ), 
            ','
         ),
         '}]'
      )   
      FROM ValuesTable vals
      WHERE vals.RequestID = hdr.[RequestID]
   )) AS request_details
FROM HeaderTable hdr
FOR JSON PATH

Statement for SQL Server 2016:

SELECT 
   hdr.RequestType AS request_type, 
   JSON_QUERY(CONCAT(
      '[{',
      STUFF(
         (
         SELECT CONCAT(',"', vals.Alias, '":"', vals.FieldValue, '"') 
         FROM ValuesTable vals
         WHERE vals.RequestID = hdr.[RequestID]
         FOR XML PATH(''), TYPE
         ).value('.', 'varchar(max)'),
         1, 1, ''
      ),
      '}]'
   )) AS request_details
FROM HeaderTable hdr
FOR JSON PATH

Result:

[
   {
      "request_type":"User Name Change",
      "request_details":[
         {
            "MobileNo":"07777777777",
            "Name":"Fred Bloggs",
            "Address":"Full address"
         }
      ]
   }
]