SQL Server get a JSON of all distinct columns grouped by a different column

Question

I have a table that looks like this:

What I want to do is convert it to a JSON that has all distinct years as keys, and an array of Make as value for each key. So based on the image, it would be something like this: { "1906": ["Reo", "Studebaker"], "1907": ["Auburn", "Cadillac", "Duryea", "Ford"], ... }

Could someone please help me on how to write a query to achieve this? I've never worked with converting SQL -> JSON before, and all I know is to use FOR JSON to convert a query to JSON.

Answer 1

I managed to solve this for whoever needs something similar. However, I think this way is inefficient and I would love for someone to tell me a better method. I first ran this SQL query:

WITH CTE_Make AS 
(
  SELECT [Year], [Make]
  FROM VehicleInformation
  GROUP BY [Year], [Make]
)
SELECT
  ( SELECT
  [Year] AS Year, 
  STRING_AGG([Make],',') AS MakeList
  FOR JSON PATH,
  WITHOUT_ARRAY_WRAPPER)
FROM CTE_Make
GROUP BY [Year]

This gave me the JSON in a different format from what I wanted. I stored this in a file "VehicleInformationInit.json", and loaded it in JS as variable vehicleInfoInitJsonFile and ran the following code in JavaScript:

var dict = {};
$.getJSON(vehicleInfoInitJsonFile, function
    (result) {
    result.forEach(x => {
        var makeList = x.MakeList.split(',');
        dict[x.Year] = makeList;
    })

    var newJSON = JSON.stringify(dict);
});

I simply copied the contents of newJSON by attaching a debugger into my required file. However, you can easily use fs and store it in a file if you want to.