Create JSON from SQL Server 2016+ (using for JSON path command)

Question

I’m trying to create some output in a specific JSON format, here’s an example of the output (will be repeated many times - once for each university):

sample json image

{
    "id": "37e556ae6b9f620d2b7262d3de971c40",
    "recordType": "school",
    "created": 1532531378,
    "updated": 1532531378,
    "published": 1532531378,
    "name": "ABC University – ABC School of Management",
    "schoolName": "ABC School of Management",
    "universityName": "ABC University",
    "sortableName": "ABC School of Management"
}

I’ve been thinking I would use SQL for this as it has the FOR JSON command which handles the JSON formatting and will deal with nesting. I can re-create the above using the following code when I hard-code the values:

SELECT 
     HASHBYTES('md5','something') AS id
     ,'school' AS recordType
     ,getdate() AS created
     ,getdate() AS updated
     ,getdate() AS published
     ,'ABC University – ABC School of Management' AS name
     ,'ABC School of Management' as schoolname 
     ,'ABC University' as universityName
     , 'ABC School of Management' as sortableName
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER 

The issue that I have is I need to create this output 100 times for different universities. I want to create a database table(s) in SQL and run something over it. Here is a sample of the data in Excel, I’ve filtered the list to only show the data needed for the above and a couple of other data points that are needed in later seperate outputs (I'm going to worry about the other outputs later):

IndiUID SchoolCode  DataValue   Ranking Band    RegionalRank
OVERALL_SCORE   XYZ     105 D   23
SCHOOL_NAME_FORMAL  XYZ XYZ Institute of Management         
SCHOOL_NAME_SORTABLE    XYZ XYZ Institute of Management         
SCHOOL_NAME_AND_BUSINESS_SCHOOL_NAME    XYZ XYZ Institute of Management, XYZ School of Business         
STUDENTS_MEDIAN_AGE XYZ 26          
OVERALL_SCORE   ABC     35  B   3
SCHOOL_NAME_FORMAL  ABC ABC School of Management            
SCHOOL_NAME_SORTABLE    ABC ABC School of Management            
SCHOOL_NAME_AND_BUSINESS_SCHOOL_NAME    ABC ABC University – ABC School of Management           
STUDENTS_MEDIAN_AGE ABC 26          

data sample image

I just can’t quite get my head around how I need to structure my table(s) so I can pull out the relevant parts using SQL. I’m wondering if I should pivot the table so that the values of ‘IndiUID’ become fields in a database table or create a new table for each of the current header items (DataValue, Ranking, Band etc).

I thought I could select from table where indiUID = OVERALL_SCORE, SCHOOL_NAME_FORMAL etc but I wonder if that is too messy.

This is where I’m stuck – how do I organise this data into tables in order to be able to be able to extract it? This is mostly about creating the JSON output, so it doesn’t matter about the DB part being a particularly elegant solution (it won't be - I'm very basic with SQL!).

Answer 1

You could try the following

Sample data:

CREATE TABLE [#school]
([school_name_and_business_school_name] VARCHAR(255), 
 [school_name]                          VARCHAR(255), 
 [university_name]                      VARCHAR(255), 
 [school_sortable]                      VARCHAR(255)
);

INSERT INTO [#school]
VALUES
('ABC University – ABC School of Management', 
 'ABC School of Management', 
 'ABC University', 
 'ABC School of Management'
),
('XYZ University – XYZ School of Management', 
 'XYZ School of Management', 
 'XYZ University', 
 'XYZ School of Management'
),
('QWE University – QWEC School of Management', 
 'QWE School of Management', 
 'QWE University', 
 'QWE School of Management'
);

And then use this query:

SELECT HASHBYTES('md5', 'something') AS [id], 
  'school' AS [recordType], 
  GETDATE() AS [created], 
  GETDATE() AS [updated], 
  GETDATE() AS [published], 
  [school_name_and_business_school_name] AS [name], 
  [school_name] AS [schoolName], 
  [university_name] AS [universityName], 
  [school_sortable] AS [sortableName]
FROM   [#school] 
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Gives you the following result:

{"id":"Q3uTDbhLgHnC3YBKcZNrXw==",
"recordType":"school",
"created":"2018-09-07T10:14:44.130",
"updated":"2018-09-07T10:14:44.130",
"published":"2018-09-07T10:14:44.130",
"name":"ABC University – ABC School of Management",
"schoolName":"ABC School of Management",
"universityName":"ABC University",
"sortableName":"ABC School of Management"
},

{"id":"Q3uTDbhLgHnC3YBKcZNrXw==",
"recordType":"school",
"created":"2018-09-07T10:14:44.130",
"updated":"2018-09-07T10:14:44.130",
"published":"2018-09-07T10:14:44.130",
"name":"XYZ University – XYZ School of Management",
"schoolName":"XYZ School of Management",
"universityName":"XYZ University",
"sortableName":"XYZ School of Management"
},

{"id":"Q3uTDbhLgHnC3YBKcZNrXw==",
"recordType":"school",
"created":"2018-09-07T10:14:44.130",
"updated":"2018-09-07T10:14:44.130",
"published":"2018-09-07T10:14:44.130",
"name":"QWE University – QWEC School of Management",
"schoolName":"QWE School of Management",
"universityName":"QWE University",
"sortableName":"QWE School of Management"
}

Does this help?