question

dinesh-3328 avatar image
0 Votes"
dinesh-3328 asked dinesh-3328 commented

JSON Value into sql server

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-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @dinesh-3328,

Welcome to Microsoft Q&A!

Please provide the expected output.Thanks.

Best regards,
Melissa

0 Votes 0 ·

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

Above data should be inserted into above two tables

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @dinesh-3328,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

dinesh-3328 avatar image
0 Votes"
dinesh-3328 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

dinesh-3328 avatar image
0 Votes"
dinesh-3328 answered MelissaMa-msft commented

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"
     }'  
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @dinesh-3328,

Thanks for your update.

Since you mentioned that my sp was working in SSMS and your tag was only related with Microsoft SQL Server, I recommend you to accept the answer if it is helpful so that your action would be helpful to other users who encounter the same issue and read this thread. 

Besides, you could post a new question in Postman related forum and you will get more professional help from many experts.

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
dinesh-3328 avatar image
0 Votes"
dinesh-3328 answered dinesh-3328 edited

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"
   }'  


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered dinesh-3328 commented

Hi @dinesh-3328,

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.


output.png (6.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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 Votes 0 ·