Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I would like to output the results of a SELECT statement as a JSON object.

id    name        active  Status
1     Bob Jones   1       Active
2     John Smith  0       Inactive
I need return result like this:
{"Active"  :[{"id":1,"name":"Bob Jones" ,"active":1}],
 "InActive":[{"id":2,"name":"John Smith","active":0}]}
How to use the query?
I am using this code
DECLARE @JSONTierDetails AS TABLE (TierDetails VARCHAR(8000))
INSERT INTO @JSONTierDetails 
    SELECT
        (SELECT id, name, active 
         FROM MyTable 
         WHERE Status = 'Active' 
         FOR JSON PATH, INCLUDE_NULL_VALUES) TierDetails
SELECT TierDetails 
FROM @JSONTierDetails
Thanks in advance
-- Table definition
DECLARE @data TABLE(id int, [name] nvarchar(128), active bit, [status] nvarchar(30));
-- Insert test rows
INSERT @data VALUES(1, 'Bob Jones', 1, 'Active'), (2, 'John Smith', 0, 'Inactive');
-- Output
SELECT
    (SELECT id, name FROM @data WHERE active = 1 FOR JSON PATH) AS [Active],
    (SELECT id, name FROM @data WHERE active = 0 FOR JSON PATH) AS [Inactive]
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER;
Array wrappers and the property names for each JSON expression are needed for your case. Adding Array wrappers is not so hard but the property names is. , ROOT(<property_name>) after FOR JSON PATH might be used but that might be applied once and in static manner.
So, a Dynamic Query might be preferred, such as 
DECLARE @JSONTierDetails NVARCHAR(MAX) = 
 SELECT 'SELECT ' + 
    STUFF((
            SELECT  N',(SELECT id, name, active 
                          FROM MyTable subT 
                         WHERE subT.status = ''' + status + N''' 
                           FOR JSON PATH
                       ) AS [' + status + N']' + CHAR(13) + '   '
              FROM MyTable t
            FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')
        + 'FOR JSON PATH, WITHOUT_ARRAY_WRAPPER' );
EXEC (@JSONTierDetails);
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.