Skip to content
Advertisement

Query response in JSON format without column name

My table schema is something like this,

DECLARE @mytable AS TABLE(ID INT, EmpName VARCHAR(10), Age INT);

INSERT INTO @mytable
VALUES
(1,     'a', 1),
(2,     'b', 2),
(3,     'c', 3),
(4,     'd', 4),
(5,     'e', 5),
(6,     'f', 6),
(7,     'g', 7),
(8,     'h', 8),
(9,     'i', 9),
(10,    'j', 10);

Initially, my procedure was returning with data as

SELECT ID, EmpName, Age FROM @mytable;

After processing the data, to the web page is sent as

[{1,"a",1},
{2,"b",2},
{3,"c",3},
{4,"d",4},
{5,"e",5},
{6,"f",6},
{7,"g",7},
{8,"h",8},
{9,"i",9},
{10,"j",10}]

If I use

SELECT ID, EmpName, Age
FROM @mytable
FOR JSON AUTO;

the result will be like

[{"ID":1,"EmpName":"a","Age":1},
 {"ID":2,"EmpName":"b","Age":2},
 {"ID":3,"EmpName":"c","Age":3},
 {"ID":4,"EmpName":"d","Age":4},
 {"ID":5,"EmpName":"e","Age":5},
 {"ID":6,"EmpName":"f","Age":6},
 {"ID":7,"EmpName":"g","Age":7},
 {"ID":8,"EmpName":"h","Age":8},
 {"ID":9,"EmpName":"i","Age":9},
 {"ID":10,"EmpName":"j","Age":10}]

So what I need to get is the same response but without column name, as front-end lib uses the JSON array without keys.

SQL Server version 2012 is used.

I hope I am clear on my end. Thanks.

Advertisement

Answer

Try this:

SELECT STUFF
(
    (
        SELECT CONCAT(',{', ID, ', "', EmpName, '", ', age, '}')
        FROM @mytable
        FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)')
    ,1
    ,1
    ,'['
) + ']';
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement