JSON Value into sql server

dinesh 41 Reputation points
2021-06-16T03:08:03.51+00:00

Hi, I have a requirement to insert values into table. I have to create stored procedure. This SP is part of API, when a user "Save" data on UI, this API will call and execute this SP. Below if json format, i get from UI. Below JSON should go into these tables..
Create table dbo.test (Quarter,Entity,Schooldetails,Teachername,Teachid,Observation)
Create table dbo.test1 (username,Age,Id,CtrName,Rank,Levelid,business) - two records will be inserted 

"Easy" : {
  "Quarter": "2nd Q"
  "Entity": "ABC"
  "Schooldetails": [{
"Username": "Dinesh,K",
"Age": "45",
"Id": "1"
    }, {
  "Username": "Dinesh,K",
   "Age": "45",
   "Id": "1"
  }
],
"Teachername": "Suni",
"Teachid" : "123",
"Additionalinfo":[{
   "CtrName": "test1",
   "Rank": "1",
   "Levelid": "lvl1",
   "business": "forest"
},{
   "CtrName": "test2",
   "Rank": "2",
   "Levelid": "lvl2",
   "business": "air"
}
],
"Observation": "1"
}
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,814 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-06-16T08:20:08.403+00:00

    Hi @dinesh ,

    set = @jsonInfo = '{ "@jsonInfo ": {' +jsonInfo + '}}'

    Could you please provide the purpose of adding above row inside the procedure?

    I tried from my side as below and you could have a check whether it is helpful. 

    create  procedure dbo.sp_jsontest  
 @jsonInfo varchar(max)  
 as  
 BEGIN  
  --set @jsonInfo = '{ "@jsonInfo ": {' +jsonInfo + '}}'  
      
  INSERT INTO dbo.test   
  SELECT   
     JSON_VALUE(@jsonInfo,'$.Easy.Quarter') AS Quarter,  
     JSON_VALUE(@jsonInfo,'$.Easy.Entity') AS Entity,  
     JSON_VALUE(@jsonInfo,'$.Easy.Schooldetails') AS Schooldetails,  
     JSON_VALUE(@jsonInfo,'$.Easy.Teachername') AS Teachername,  
     JSON_VALUE(@jsonInfo,'$.Easy.Teachid') AS Teachid,  
     JSON_VALUE(@jsonInfo,'$.Easy.Observation') AS Observation  

   ;with cte as   
  (SELECT *,ROW_NUMBER() over (order by (select null)) rn   
  FROM OPENJSON(@jsonInfo, '$.Easy.Schooldetails') WITH (  
  Username VARCHAR(10) '$.Username',  
  Age INT '$.Age',  
  Id INT '$.Id'))  
  ,cte1 as   
  (SELECT *,ROW_NUMBER() over (order by (select null)) rn    
  FROM OPENJSON(@jsonInfo, '$.Easy.Additionalinfo') WITH (  
  CtrName VARCHAR(10) '$.CtrName',  
  Rank INT '$.Rank',  
  Levelid VARCHAR(10) '$.Levelid',  
  business VARCHAR(10) '$.business'))  
   INSERT INTO dbo.test1   
   select a.Username,a.Age,a.Id,b.CtrName,b.Rank,b.Levelid,b.business  
  from cte a   
  full join cte1 b on a.rn=b.rn  
 end

    Then call this function as below: 

     exec sp_jsontest '{    
  "Easy" : {  
  "Quarter": "2nd Q",  
  "Entity": "ABC",  
  "Schooldetails": [{  
  "Username": "Dinesh,K",  
  "Age": "45",  
  "Id": "1"  
  }, {  
  "Username": "Dinesh,K",  
  "Age": "45",  
  "Id": "1"}  
  ],  
  "Teachername": "Suni",  
  "Teachid" : "123",  
  "Additionalinfo":[{  
  "CtrName": "test1",  
  "Rank": "1",  
  "Levelid": "lvl1",  
  "business": "forest"  
  },{  
  "CtrName": "test2",  
  "Rank": "2",  
  "Levelid": "lvl2",  
  "business": "air"}  
  ],  
  "Observation": "1"  
   }'

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-06-16T05:17:27.62+00:00

    Hi @dinesh ,

    Please refer below: 

     DROP TABLE IF EXISTS test,test1  
  
Create table dbo.test   
(Quarter VARCHAR(10),  
Entity VARCHAR(10),  
Schooldetails VARCHAR(10),  
Teachername VARCHAR(10),  
Teachid INT,  
Observation INT)  
  
Create table dbo.test1   
(username VARCHAR(10),  
Age INT,  
Id INT,  
CtrName VARCHAR(10),  
Rank INT,  
Levelid VARCHAR(10),  
business VARCHAR(10))  

DECLARE @jsonInfo NVARCHAR(MAX)  
  
SET @jsonInfo=N'{    
"Easy" : {  
"Quarter": "2nd Q",  
"Entity": "ABC",  
"Schooldetails": [{  
"Username": "Dinesh,K",  
"Age": "45",  
"Id": "1"  
}, {  
"Username": "Dinesh,K",  
"Age": "45",  
"Id": "1"}  
],  
"Teachername": "Suni",  
"Teachid" : "123",  
"Additionalinfo":[{  
"CtrName": "test1",  
"Rank": "1",  
"Levelid": "lvl1",  
"business": "forest"  
},{  
"CtrName": "test2",  
"Rank": "2",  
"Levelid": "lvl2",  
"business": "air"}  
],  
"Observation": "1"  
 }'    

INSERT INTO dbo.test   
SELECT   
   JSON_VALUE(@jsonInfo,'$.Easy.Quarter') AS Quarter,  
   JSON_VALUE(@jsonInfo,'$.Easy.Entity') AS Entity,  
   JSON_VALUE(@jsonInfo,'$.Easy.Schooldetails') AS Schooldetails,  
   JSON_VALUE(@jsonInfo,'$.Easy.Teachername') AS Teachername,  
   JSON_VALUE(@jsonInfo,'$.Easy.Teachid') AS Teachid,  
   JSON_VALUE(@jsonInfo,'$.Easy.Observation') AS Observation  

 ;with cte as   
(SELECT *,ROW_NUMBER() over (order by (select null)) rn   
FROM OPENJSON(@jsonInfo, '$.Easy.Schooldetails') WITH (  
Username VARCHAR(10) '$.Username',  
Age INT '$.Age',  
Id INT '$.Id'))  
,cte1 as   
(SELECT *,ROW_NUMBER() over (order by (select null)) rn    
FROM OPENJSON(@jsonInfo, '$.Easy.Additionalinfo') WITH (  
CtrName VARCHAR(10) '$.CtrName',  
Rank INT '$.Rank',  
Levelid VARCHAR(10) '$.Levelid',  
business VARCHAR(10) '$.business'))  
 INSERT INTO dbo.test1   
 select a.Username,a.Age,a.Id,b.CtrName,b.Rank,b.Levelid,b.business  
from cte a   
full join cte1 b on a.rn=b.rn  

select * from test  
select * from test1

    Output:
    106044-output.png

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. dinesh 41 Reputation points
    2021-06-16T08:10:26.15+00:00

    I have created SP - 

    created procedure dbo.jsontest
@jsonInfo varchar(max)
as
BEGIN
 set = @jsonInfo = '{ "@jsonInfo ": {' +jsonInfo + '}}'

 INSERT INTO dbo.test 
 SELECT 
    JSON_VALUE(@jsonInfo,'$.Easy.Quarter') AS Quarter,
    JSON_VALUE(@jsonInfo,'$.Easy.Entity') AS Entity,
    JSON_VALUE(@jsonInfo,'$.Easy.Schooldetails') AS Schooldetails,
    JSON_VALUE(@jsonInfo,'$.Easy.Teachername') AS Teachername,
    JSON_VALUE(@jsonInfo,'$.Easy.Teachid') AS Teachid,
    JSON_VALUE(@jsonInfo,'$.Easy.Observation') AS Observation
  ;with cte as 
 (SELECT *,ROW_NUMBER() over (order by (select null)) rn 
 FROM OPENJSON(@jsonInfo, '$.Easy.Schooldetails') WITH (
 Username VARCHAR(10) '$.Username',
 Age INT '$.Age',
 Id INT '$.Id'))
 ,cte1 as 
 (SELECT *,ROW_NUMBER() over (order by (select null)) rn  
 FROM OPENJSON(@jsonInfo, '$.Easy.Additionalinfo') WITH (
 CtrName VARCHAR(10) '$.CtrName',
 Rank INT '$.Rank',
 Levelid VARCHAR(10) '$.Levelid',
 business VARCHAR(10) '$.business'))
  INSERT INTO dbo.test1 
  select a.Username,a.Age,a.Id,b.CtrName,b.Rank,b.Levelid,b.business
 from cte a 
 full join cte1 b on a.rn=b.rn
end

    I have associated above SP with an API by sending below raw data in postman but throwing an error: Procedure or function 'jsontest' expects parameter '@jsonInfo', which was not supplied 

    '{  
 "Easy" : {
 "Quarter": "2nd Q",
 "Entity": "ABC",
 "Schooldetails": [{
 "Username": "Dinesh,K",
 "Age": "45",
 "Id": "1"
 }, {
 "Username": "Dinesh,K",
 "Age": "45",
 "Id": "1"}
 ],
 "Teachername": "Suni",
 "Teachid" : "123",
 "Additionalinfo":[{
 "CtrName": "test1",
 "Rank": "1",
 "Levelid": "lvl1",
 "business": "forest"
 },{
 "CtrName": "test2",
 "Rank": "2",
 "Levelid": "lvl2",
 "business": "air"}
 ],
 "Observation": "1"
  }'
    0 comments
  3. dinesh 41 Reputation points
    2021-06-16T08:32:10.273+00:00

    I tried your SP and it is working fine in Management studio but when I try this is postman, it is throwing same error: Procedure or function 'jsontest' expects parameter '@jsonInfo', which was not supplied

    This is what i supplied... 

    '{  
   "Easy" : {
   "Quarter": "2nd Q",
   "Entity": "ABC",
   "Schooldetails": [{
   "Username": "Dinesh,K",
   "Age": "45",
   "Id": "1"
   }, {
   "Username": "Dinesh,K",
   "Age": "45",
   "Id": "1"}
   ],
   "Teachername": "Suni",
   "Teachid" : "123",
   "Additionalinfo":[{
   "CtrName": "test1",
   "Rank": "1",
   "Levelid": "lvl1",
   "business": "forest"
   },{
   "CtrName": "test2",
   "Rank": "2",
   "Levelid": "lvl2",
   "business": "air"}
   ],
   "Observation": "1"
    }'
  4. dinesh 41 Reputation points
    2021-06-16T11:58:17.493+00:00

    Thanks a lot for all your help.

    Finally small help required. I have added "Category"field in test1 table. I want to insert specify category='Schooldetails' while inserting. There should be records. Similarly, category='Additionalinfo', here also i have to insert 2 records. Whatever it count comes those many records should be inserted into 'test1' table.

    What changes required in above Store procedure?

    0 comments