Get Json Output with two select query in SQL Server

Question

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

Answer 1

-- 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;

Answer 2

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);

Demo

Answer 3

see this page in microsoft site: JSON Data (SQL Server)