SQL Server root + FOR JSON Path in the same object

Question

I am trying to retrieve some data in JSON, however, I am struggling to format it in the correct way, here an example:

CREATE TABLE #test (id int identity(1,1), name varchar(100), EntityType VARCHAR(10))

insert into #test values('Dell','PC')
insert into #test values('Microsoft','CO')
insert into #test values('MAC','PC')
insert into #test values('APPLE','CO')

SELECT * FROM #test WHERE EntityType = 'PC' FOR JSON PATH, ROOT('??')  

drop table #test

I have been trying using root but unsuccessfully

I am looking for this result, it is an object and then an array group by a column

{
    "CO": [
      {
          "id": 1,
          "name": "Dell",
      },
      {
          "id": 2,
          "name": "Microsoft",
      },
    ]
    "PC" :[
      {
        "id": 3,
        "name": "MAC",
    },
    {
        "id": 4,
        "name": "APPLE",
    }]
}

Yitzhak Khabinsky · Accepted Answer · 2021-01-19 18:41:55Z

Another approach without dynamic SQL.

And root value on the EntityType level is coming from the table.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY key, [name] varchar(100), EntityType VARCHAR(10));
INSERT INTO @tbl VALUES
('Dell','PC'),
('Microsoft','CO'),
('MAC','PC'),
('APPLE','CO');
-- DDL and sample data population, end

SELECT CONCAT(
   N'{',
   STUFF(
      (
      SELECT DISTINCT CONCAT(N',"', k.EntityType, '":', c.[Json])
      FROM @tbl AS k
      CROSS APPLY (
         SELECT id, [name]
         FROM @tbl
         WHERE EntityType = k.EntityType
         FOR JSON PATH
      ) c([Json])
      FOR XML PATH('')
      ), 1, 1, N''
   ),  
   N'}'
) AS JsonOutput;

Output

{
    "CO": [
        {
            "id": 2,
            "name": "Microsoft"
        },
        {
            "id": 4,
            "name": "APPLE"
        }
    ],
    "PC": [
        {
            "id": 1,
            "name": "Dell"
        },
        {
            "id": 3,
            "name": "MAC"
        }
    ]
}

critical_error · Accepted Answer · 2021-01-19 18:08:46Z

0

Here are two ways you can go about this depending on your need.

-- VIA FIXED VALUE --

SELECT [id], [name] FROM Test WHERE EntityType = 'PC'
    FOR JSON PATH, ROOT( 'PC' );

-- VIA DYNAMIC SQL --

DECLARE @type varchar(2) = 'PC';
DECLARE @sql nvarchar(MAX) = 'SELECT [id], [name] FROM Test WHERE EntityType=''' + @type + ''' FOR JSON PATH, ROOT(''' + @type + ''');';

    EXEC ( @sql );

Both examples return the following JSON:

{
    "PC": [{
        "id": 1,
        "name": "Dell"
    }, {
        "id": 3,
        "name": "MAC"
    }]
}

The Dynamic SQL is handy in the event you want generic functionality that can be reused for different EntityType definitions.

edited Jan 19, 2021 at 18:08

answered Jan 19, 2021 at 17:40

critical_error

6,8263 gold badges16 silver badges17 bronze badges

Collectives™ on Stack Overflow

SQL Server root + FOR JSON Path in the same object

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related