Set a "root" for each item in a SQL Server JSON result item?

user_186750 0
Viewed 108

I have data stored in a database like so:

name VARCHAR(20),
host VARCHAR(200),
username varchar(50),
password varchar(50)

I can run the following as a query

select name,
host,
username as 'credentials.username',
password as 'credentials.password'
from tablename
 FOR JSON PATH

which gives me a JSON like:

[
  {
    name:'MyItem',
    host:'www.google.com',
    credentials: {
       username: 'usernamevalue',
       password: 'passwordvalue'
    }
  }
]

What I would like to get:

[
   MyItem: {
    host:'www.google.com',
    credentials: {
       username: 'usernamevalue',
       password: 'passwordvalue'
    }
  }
]

If I use a ROOT after the FOR JSON PATH, it will set that outside the array as the root name.

Essentially, I want each item in the array returned to be named for a value. In this case, the name field in the select statement.

Is this possible?

sql-server json
1 Answers

SQL Server is declarative by design. This means you would either have to use Dynamic SQL or some string manipulation

Example

Declare @YourTable Table ([name] varchar(50),[host] varchar(50),[username] varchar(50),[password] varchar(50))  Insert Into @YourTable Values 
 ('MyItem','www.google.com','usernamevalue','passwordvalue')
,('NextItem','www.askjeeves.com','SomeOldGuy','MyPassword')

 ;with cte as (
 Select name,JS=JSON_QUERY( (Select host
                                   ,[credentials.username]=username 
                                   ,[credentials.password]=password 
                               FOR JSON PATH,Without_Array_Wrapper  
                          ) )
 From @YourTable
)
Select '['+string_agg('{"'+name+'":'+JS,'},')+'}]'
 From  cte

Returns

[{
    "MyItem": {
        "host": "www.google.com",
        "credentials": {
            "username": "usernamevalue",
            "password": "passwordvalue"
        }
    }
}, {
    "NextItem": {
        "host": "www.askjeeves.com",
        "credentials": {
            "username": "SomeOldGuy",
            "password": "MyPassword"
        }
    }
}]

If NOT 2017+ ...

;with cte as (
 Select name,JS=JSON_QUERY( (Select host
                                   ,[credentials.username]=username 
                                   ,[credentials.password]=password 
                               FOR JSON PATH,Without_Array_Wrapper  
                          ) )
 From @YourTable
)
Select '[' +stuff((select '},{"'+name+'":'+ +JS From cte For XML Path ('')),1,2,'') +'}]'
user_15700000
Related