4

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?

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
Salem
  • 119
  • 1
  • 11

1 Answers1

2

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

Salem
  • 119
  • 1
  • 11
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • Thanks @Roman The second solution using the `string_agg` worked for me, but the first one didn't work (I'm using sql server 2017) it threw the following error message: `Msg 13607, Level 16, State 4, Line 3 JSON path is not properly formatted. Unexpected character '2' is found at position 2` – Salem Apr 14 '19 at 17:49
  • @SalemWafi can you create a db fiddle with your example? – Roman Pekar Apr 14 '19 at 19:31
  • I was trying to actually, but sqlfiddle.com is down and db-fiddle.com doesn't support MS SQL Databases. However, I have the script on db-fiddle website so you can test it at whatever platform that you like which supports MS SQL. https://www.db-fiddle.com/f/kdYGDDRV9hQPi8x6jkFCX5/1 – Salem Apr 15 '19 at 05:43
  • 1
    Arr you sure sqlfiddle example and the one you trying on your local server are the same? – Roman Pekar Apr 22 '19 at 08:52
  • Nope actually they are not exactly the same, I just mimicked the schema in sql fiddle and I got the same error there too. Here is the example of it http://sqlfiddle.com/#!18/51993/2 – Salem Apr 22 '19 at 17:30
  • 1
    @RomanPekar You need to escape the `path` to correct this `Msg 13607, Level 16, State 4, Line 3 JSON path is not properly formatted. Unexpected character '2' is found at position 2` error in your first solution. Change `concat('$.', p.key_code)` to `concat('$."', p.key_code, '"')`. It's a good answer, +1. – Zhorov Oct 04 '19 at 16:50
  • Thanks @Zhorov !! Your edit fixed the issue, here is a fixed fiddle example: http://sqlfiddle.com/#!18/51993/3 – Salem Feb 10 '21 at 23:15