CODEWITHSUNDEEP
jsonsql-serverdatabase
Muhammed Caylak
Muhammed Caylak

Reputation: 353

How to dynamically create this json format?

Hello everyone, good work first,

I want to create a json model like the one below.

1- Data name has been created historically here. I don't know how to create it, how is this possible?

2- My other question is, how can I store this data in a database in a table?

Thanks to everyone already.

10/01/2020": {
"Confirmed": 0,
"Recovered": 0,
"Death": 0 },


"11/01/2020": {
"Confirmed": 0,
"Recovered": 0,
"Death": 0},

...

Upvotes: 0

Views: 71

Answers (1)

lptr
lptr

Reputation: 6788

create table dbo.HospitalAdmissionStat
(
thedate date,
confirmed int,
recovered int,
death int,
createdutc datetime2(3) constraint dfcreatedutcmydata default(sysutcdatetime())
index clidxthedate clustered (thedate)
);

--go


declare @json nvarchar(max) = N'{
"10/01/2020": {
"Confirmed": 10,
"Recovered": 10,
"Death": 0 },

"11/01/2020": {
"Confirmed": 11,
"Recovered": 1,
"Death": 1}, 

"12/01/2020": {
"Confirmed": 12,
"Recovered": 2,
"Death": 2}, 

"13/01/2020": {
"Confirmed": 13,
"Recovered": 3,
"Death": 3},

"14/01/2020": {
"Confirmed": 14,
"Recovered": 0,
"Death": 0}

}
';

insert into dbo.HospitalAdmissionStat(thedate, confirmed, recovered, death)
select convert(date, dt.[key], 103) as thedate, v.Confirmed, v.Recovered, v.Death
from openjson(@json) as dt
cross apply openjson(dt.[value])
with
(
Confirmed int,
Recovered int,
Death int
) as v
order by thedate;

select *
from dbo.HospitalAdmissionStat;

Upvotes: 1

Related Questions