How to make the field values of a SQL table to be the keys of JSON object in SQL?

Question

I have been trying to get a JSON object from my DB in the format that I wanted it so I ran the following sql query:

SELECT PROJECTS.key_code AS CODE, PROJECTS.name AS Name,
         PROJECTS.date AS Date, PROJECTS.descr AS Description
         FROM PROJECTS LEFT JOIN ACCESS
         ON PROJECTS.key_code = ACCESS.key_code
         WHERE ACCESS.Ukey_code = '5d8hd5' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

and the result of the query as follow:

{
  "Code": "h5P93G",
  "Name": "Project1 test name",
  "Date": "2017-09-03",
  "Description": "This is a test description 1"
 },
  "Code": "KYJ482",
  "Name": "Project2 test name",
  "Date": "2018-10-25",
  "Description": "This is a test description 2"
}

but actually what I want is different. The JSON object should look like:

{
  "h5P93G": {
          "Name": "Project1 test name",
          "Date": "2017-09-03",
          "Description": "This is a test description 1"
        },
  "KYJ482": {
          "Name": "Project2 test name",
          "Date": "2018-10-25",
          "Description": "This is a test description 2"
        },
}

So, how I could get this JSON object?

Answer 1

As far as I know, you cannot really create JSON with variable key names with select ... for json.

However, if you don't mind using variables and you're using SQL Server 2017 (otherwise you cannot use dynamic keys for json-modify), you can do it like this:

declare @a nvarchar(max) = '{}'

select
    @a = json_modify(
        @a,
        concat('$."', p.key_code, '"'), /*This line fixed by @Zhorov*/
        json_query((select p.name, p.[date], p.descr for json path, without_array_wrapper))
    )
from projects as p

select @a

db fiddle demo

If you're using earlier editions of SQL Server, you can just aggregate it with any aggregation method you can find (I've used string_agg just for simplicity here):

select
    concat('{', string_agg(
        concat('"',p.key_code,'":',p.data),
        ','
    ), '}')
from (
    select
        p.key_code,
        (select p.name, p.[date], p.descr for json path, without_array_wrapper) as data
    from projects as p
) as p

db fiddle demo

You might also consider using string_escape to prevent errors in case your keys can contain special characters:

select
    ...
        concat('"',string_escape(p.key_code,'json'),'":',p.data),
        ','
    ...

db fiddle demo