how to Convert Table to Json Arrays?

First of all: JSON support needs v2016+. Secondly: The problem here will be the naked array like here "Number": ["1","2","3"]. I have no idea why, but that is not supported at the moment. The rest is rather easy, but this will need some tricks.

Try this

DECLARE @tmp TABLE(    [Color] [nvarchar](50) NULL,    [Type] [nvarchar](50) NULL,    [Number] [nvarchar](50) NULL)INSERT INTO @tmp ([Color], [Type], [Number]) VALUES  (N'Blue', N'A', N'1'),(N'Blue', N'A', N'2'),(N'Blue', N'A', N'3'),(N'Blue', N'B', N'1'),(N'Blue', N'C', N'1'),(N'Red', N'A', N'1'),(N'Red', N'B', N'2');SELECT t.Color     ,(        SELECT t2.[Type]              ,(                SELECT t3.Number                FROM @tmp t3                WHERE t3.Color=t.Color AND t3.[Type]=t2.[Type]                FOR JSON PATH               ) AS Number        FROM @tmp t2        WHERE t2.Color=t.Color        GROUP BY t2.[Type]        FOR JSON PATH      ) AS PartFROM @tmp tGROUP BY t.ColorFOR JSON PATH;

the result (formatted)

[    {        "Color": "Blue",        "Part": [            {                "Type": "A",                "Number": [                    {                        "Number": "1"                    },                    {                        "Number": "2"                    },                    {                        "Number": "3"                    }                ]            },            {                "Type": "B",                "Number": [                    {                        "Number": "1"                    }                ]            },            {                "Type": "C",                "Number": [                    {                        "Number": "1"                    }                ]            }        ]    },    {        "Color": "Red",        "Part": [            {                "Type": "A",                "Number": [                    {                        "Number": "1"                    }                ]            },            {                "Type": "B",                "Number": [                    {                        "Number": "2"                    }                ]            }        ]    }]

Now we have to use rather ugly tricks with REPLACE to get rid of the array of objects in the middle:

SELECT REPLACE(REPLACE(REPLACE((    SELECT t.Color         ,(            SELECT t2.[Type]                  ,(                    SELECT t3.Number                    FROM @tmp t3                    WHERE t3.Color=t.Color AND t3.[Type]=t2.[Type]                    FOR JSON PATH                   ) AS Number            FROM @tmp t2            WHERE t2.Color=t.Color            GROUP BY t2.[Type]            FOR JSON PATH          ) AS Part    FROM @tmp t    GROUP BY t.Color    FOR JSON PATH),'},{"Number":',','),'{"Number":',''),'}]}',']}');

the result

[    {        "Color": "Blue",        "Part": [            {                "Type": "A",                "Number": [                    "1",                    "2",                    "3"                ]            },            {                "Type": "B",                "Number": [                    "1"                ]            },            {                "Type": "C",                "Number": [                    "1"                ]            }        ]    },    {        "Color": "Red",        "Part": [            {                "Type": "A",                "Number": [                    "1"                ]            },            {                "Type": "B",                "Number": [                    "2"                ]            }        ]    }]

UPDATE

It might be a bit easier and cleaner to create the naked array on string level:

SELECT t.Color        ,(        SELECT t2.[Type]                ,JSON_QUERY('[' + STUFF((                SELECT CONCAT(',"',t3.Number,'"')                FROM @tmp t3                WHERE t3.Color=t.Color AND t3.[Type]=t2.[Type]                FOR XML PATH('')),1,1,'') + ']') AS Number        FROM @tmp t2        WHERE t2.Color=t.Color        GROUP BY t2.[Type]        FOR JSON PATH        ) AS PartFROM @tmp tGROUP BY t.ColorFOR JSON PATH;

UPDATE 2: Starting with v2017 there is STRING_AGG()

You can try this on v2017

SELECT t.Color        ,(        SELECT t2.[Type]                ,JSON_QUERY('["' + STRING_AGG(t2.Number,'","') + '"]') AS Number        FROM @tmp t2        WHERE t2.Color=t.Color        GROUP BY t2.[Type]        FOR JSON PATH        ) AS PartFROM @tmp tGROUP BY t.ColorFOR JSON PATH;