CODEWITHSUNDEEP
sqlt-sql
cj devin
cj devin

Reputation: 1375

SQL query construction as json object from multiple column

I am try to get result like combine multiple column as single column in result but not got success. how to get exepected result?

query:

 select SchemaName,([create],[read],[update],[delete]) as [Permissions] from ApplicationRoles for json path;

Exepected Result:

{
  "SchemaName": "TestSchema",
  "Permissions": {
    "create": true,
    "read": true,
    "update": true,
    "delete": true
  }
}

Thanks in advance.

Upvotes: 0

Views: 38

Answers (1)

Thom A
Thom A

Reputation: 95564

Put the values you want in an additional array in a subquery:

CREATE TABLE dbo.YourTable(SchemaName sysname,
                           [create] bit,
                           [read] bit)
INSERT INTO dbo.YourTable
VALUES ('TestSchema',1,1);
GO
SELECT YT.SchemaName,
       (SELECT YT.[create],
               YT.[read]
        FOR JSON PATH) AS Permissions --No WITHOUT_ARRAY_WRAPPER as this will result in "Permissions": "{\"create\":true,\"read\":true}"
FROM dbo.YourTable YT
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;
GO
DROP TABLE dbo.YourTable;

Upvotes: 1

Related Questions