convert rows/table records to json document in sql server

Question

the following is the sample data which is an input

if object_id('tempdb.dbo.#store_data') is not null
drop table #store_data

create table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100)
)

INSERT INTO #store_data VALUES ('sid','1','1')
INSERT INTO #store_data VALUES ('bid','3','1');
INSERT INTO #store_data VALUES ('time','2019-01-01','1');
INSERT INTO #store_data VALUES ('que','apple','1');
INSERT INTO #store_data VALUES ('sid','2','2');
INSERT INTO #store_data VALUES ('bid','5','2');
INSERT INTO #store_data VALUES ('hrs','6','2');
INSERT INTO #store_data VALUES ('dat','pine','2');

select * from #store_data

the following is the result required

[{
"sid"="1",
"bid"="3",
"time"="2019-01-01"
"que"="apple"},
{"sid"="2",
"bid"="5",
"hrs"="6",
"dat"="pine"
}]

the following is the query which i have tried

select [key],[value] from #store_data for json path

expected results were not achieved.

You should be aware of the fact, that there is no implicit sort order! Without an outermost ORDER BY this can work hundred times, pass all internal tests, but will return rubbish in production. It is nothing then random if bid=3 appears in the first or in the second JSON object. — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 29, 2019 at 9:28
I have updated my answer @Shnugo...given by PSK...I have used loops to get the result set...but i felt is there another way for retrieving in same order — Smart003
– Smart003, Commented Jan 29, 2019 at 10:39
Loop or not: A SQL-Server is not sorted the way you insert your data. Any SELECT without an outermost ORDER BY can return in any random sort order. If you want to persist the sort order, the easiest is to add an IDENTITY column. — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 29, 2019 at 11:54

PSK · Accepted Answer · 2019-01-29 10:54:30Z

3

Please note, you can't get with direct query, you have to first PIVOT on storeid.

You can try like following query.

select sid, bid, time,que, hrs,dat 
from #store_data src
pivot
(
  MAX([value])
  for [key] in (sid, bid, time,que, hrs,dat)
) piv
for json auto

Full Example

 if object_id('tempdb.dbo.#store_data') is not null
 drop table #store_data

create table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100))

INSERT INTO #store_data VALUES ('sid','1','1')
INSERT INTO #store_data VALUES ('bid','3','1');
INSERT INTO #store_data VALUES ('time','2019-01-01','1');
INSERT INTO #store_data VALUES ('que','apple','1');
INSERT INTO #store_data VALUES ('sid','2','2');
INSERT INTO #store_data VALUES ('bid','5','2');
INSERT INTO #store_data VALUES ('hrs','6','2');
INSERT INTO #store_data VALUES ('dat','pine','2');

select sid, bid, time,que, hrs,dat 
from #store_data src
pivot
(
  MAX([value])
  for [key] in (sid, bid, time,que, hrs,dat)
) piv
for json auto

Output:

[
  {
    "sid": "1",
    "bid": "3",
    "time": "2019-01-01",
    "que": "apple"
  },
  {
    "sid": "2",
    "bid": "5",
    "hrs": "6",
    "dat": "pine"
  }
]

Online Demo:

Here

Edit:

PIVOT is automatically ordering on stroeid ASC without specifying any order. In case you want to change it to a different order let say DESC stroeid, in that case you can change the query as following.

select sid, bid, time,que, hrs,dat 
from #store_data src
pivot
(
  MAX([value])
  for [key] in (sid, bid, time,que, hrs,dat)
) piv
order by storeid desc
for json auto

edited Jan 29, 2019 at 10:54

answered Jan 29, 2019 at 8:45

PSK

18k5 gold badges35 silver badges44 bronze badges

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

6 Comments

Gottfried Lesigang Over a year ago

Hi, Although this might be the correct approach, we cannot get tired to point to the fact, that there is no implicit sort order! Without an outermost ORDER BY this can work hundred times, pass all internal tests, but will return rubbish in production. It is nothing then random if bid=3 appears in the first or in the second JSON object.

PSK Over a year ago

Hi @Shnugo , I am not sure if I am getting the point. Your comment is beyond my knowledge. Can you please explain it little more or some sample data where it can re produce the scenario

Gottfried Lesigang Over a year ago

Okay, I was a bit to fast, because I did not see the storeid on the very first sight, which is included by SELECT *. Together with this information, the objects will get the correct values (as group). Without this storeid as a part of the final JSON it still holds true, that you have no guarantee, which JSON-object will appear first and which will appear second. But this is not really against your approach (+1 from my side)

Smart003 Over a year ago

I am happy that you have identified the point "order of display" @Shnugo.....I request PSK and Shnugo is there way in the query without using while loop to display the results in same order.

PSK Over a year ago

@Smart003, i have edited the answer, you can have a look.

|

gotqn · Accepted Answer · 2019-01-29 08:45:54Z

2

Try this:

drop table if exists #store_data;

create table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100)
)

INSERT INTO #store_data VALUES ('sid','1','1')
INSERT INTO #store_data VALUES ('bid','3','1');
INSERT INTO #store_data VALUES ('time','2019-01-01','1');
INSERT INTO #store_data VALUES ('que','apple','1');
INSERT INTO #store_data VALUES ('sid','2','2');
INSERT INTO #store_data VALUES ('bid','5','2');
INSERT INTO #store_data VALUES ('hrs','6','2');
INSERT INTO #store_data VALUES ('dat','pine','2');

DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@ColumnNames NVARCHAR(MAX);

SELECT @ColumnNames = STUFF
(
    (
        SELECT DISTINCT ',[' + [key] + ']'
        FROM #store_data
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
   ,1
   ,1
   ,''
);

SET @DynamicTSQLStatement = N'
select ' + @ColumnNames + '
from #store_data
PIVOT 
(
    MAX([value]) FOR [key] IN (' + @ColumnNames + ')
) PVT
FOR JSON PATH;
';


EXEC sp_executesql @DynamicTSQLStatement;

Change to SELECT * in order to get the storeid in the JSON object, too. Also, in SQL Server 2017 you can use STRING_AGG to concatenate the keys.

edited Jan 29, 2019 at 8:45

answered Jan 29, 2019 at 8:40

gotqn

44k47 gold badges167 silver badges257 bronze badges

7 Comments

Panagiotis Kanavos Over a year ago

The query is tagged SQL Server 2017

gotqn Over a year ago

Yes, that's why I am saying that he can use STRING_AGG.

Panagiotis Kanavos Over a year ago

Why post the XML concatenation technique then? It hides the actual solution, which is to PIVOT the data before converting them to JSON

Gottfried Lesigang Over a year ago

You might read my comment below the question about the non-existant implict sort order..

gotqn Over a year ago

@Shnugo Yes, you are right. That's why I pointed we can use SELECT * in order to get the storeid in each object. It seems normal to have it there, because it is used to group the data.

|

Collectives™ on Stack Overflow

convert rows/table records to json document in sql server

2 Answers 2

6 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related