My table schema is something like this, Initially, my procedure was returning with data as After processing the data, to the web page is sent as If I use the result will be like 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

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.

Answer

Try this:

SELECT STUFF
(
    (
        SELECT CONCAT(',{', ID, ', "', EmpName, '", ', age, '}')
        FROM @mytable
        FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)')
    ,1
    ,1
    ,'['
) + ']';

Advertisement

Answer