Query response in JSON format without column name

Question

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.

@gotqn, Sorry, my home computer have sql server 2017 installed so I did not realize that it will not work on 2012 on which the project DB is. Any other options? — Harsh Shankar
– Harsh Shankar, Commented Feb 3, 2019 at 10:18
@Harsh . . . If you are using your home computer for development, you might want to learn about database compatibility levels: learn.microsoft.com/en-us/sql/t-sql/statements/…. — Gordon Linoff
– Gordon Linoff, Commented Feb 3, 2019 at 12:31
@GordonLinoff No, I am not using my home computer for development but since I was stuck with the problem in office so I was trying to solve it during weekend. About changing compatibility level, I will change that in my home PC's database. Thanks for the input. :) — Harsh Shankar
– Harsh Shankar, Commented Feb 3, 2019 at 12:42

gotqn · Accepted Answer · 2019-02-03 16:03:34Z

4

Try this:

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

edited Feb 3, 2019 at 16:03

answered Feb 3, 2019 at 10:13

gotqn

44k47 gold badges168 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LukStorms · Accepted Answer · 2019-02-19 07:36:07Z

1

You could use the "select to a variable" trick to build the string.

declare @JsonesceString varchar(max);

select @JsonesceString = concat(@JsonesceString+','+char(10),'{',ID,',"',EmpName,'",',age,'}')
from @mytable
order by ID;

select concat('[',@JsonesceString,']') as Result;

Returns the value:

[{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}]

edited Feb 19, 2019 at 7:36

answered Feb 3, 2019 at 16:04

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Collectives™ on Stack Overflow

Query response in JSON format without column name

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related