SQL Server - Generate Dynamic JSon output

Question

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?

Do you know all possible values, stored in the Alias column or they can vary? — Zhorov
– Zhorov, Commented Jan 12, 2022 at 15:20
The values are all held in another table so I do know them, yes. — chilluk
– chilluk, Commented Jan 12, 2022 at 15:26
Are you sure about the sample data in the ValuesTable, especially the rows with RequestID equal to 2 and this WHERE vals.RequestID = hdr.[RequestID] clause? — Zhorov
– Zhorov, Commented Jan 12, 2022 at 15:44
Yes sorry I had missed a header record - I want to lump all like request types together and have the values records make up the request_details lines — chilluk
– chilluk, Commented Jan 12, 2022 at 16:16
Please show that other table (columns and sample data), containing Alias values — Charlieface
– Charlieface, Commented Jan 12, 2022 at 16:38

Zhorov · Accepted Answer · 2022-01-13 06:50:54Z

2

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

edited Jan 13, 2022 at 6:50

answered Jan 12, 2022 at 15:51

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

3 Comments

chilluk Over a year ago

Looks promising - we are on SQL Server 2016 (sorry should have said) so STRING_AGG not currently available?

Zhorov Over a year ago

@chilluk, for SQL Server 2016 you need to use FOR XML PATH.

chilluk Over a year ago

Thanks Zhorov awesome that seems to do what I want it to

Collectives™ on Stack Overflow

SQL Server - Generate Dynamic JSon output

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related