时间戳

首页 > 解决方案 > 将 JSON 值转换为 SQL Server

sql - 将 JSON 值转换为 SQL Server

问题描述

我需要将值插入表中。我必须创建存储过程。这个 SP 是 API 的一部分，当用户在 UI 上“保存”数据时，这个 API 会调用并执行这个 SP。下面如果 JSON 格式，我从 UI 中得到。

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

我试过这个但没有用。它抛出一个错误......当我尝试使用邮递员时。邮递员的错误信息：

JSON 文本格式不正确。在第 9 位发现意外字符“{”。

CREATE PROCEDURE DBO.TEST @easy VARCHAR(max) 
as
Begin
  Set @json = '{ "Easy": {' + @easy + '} }'

begin try
 begin trans

insert into dbo.test 
 (
      Quarter,
      Entity,
      Schooldetails, 
      Teachername,
      Teachid,
      Observation
)
(
select JSON_Value (a.value, '$.Quarter')  Quarter,
JSON_Value (a.value, '$.Entity')  Entity,
JSON_Value (a.value, '$.Schooldetails')  Schooldetails,
JSON_Value (a.value, '$.Teachername')  Teachername,
JSON_Value (a.value, '$.Teachid')  Teachid,
JSON_Value (a.value, '$.Observation')  Observation from OPENJSON(@json, '$.easy') as easy

insert into dbo.test1
 (
     username,
     Age,
     Id,
     CtrName,
     Rank,
     Levelid,
     business
)
(
select JSON_Value (a.value, '$.username')  username,
JSON_Value (a.value, '$.Age')  Age,
JSON_Value (a.value, '$.Id')  Id,
JSON_Value (a.value, '$.CtrName)  CtrName,
JSON_Value (a.value, '$.Rank')  Rank,
JSON_Value (a.value, '$.Levelid')  Levelid,
JSON_Value (a.value, '$.business')  business
 from OPENJSON(@json, '$.easy') as easy1 CROSS APPLY OPENJSON(easy1.value, '$.Schooldetails') as b
)

insert into dbo.test1
 (
     username,
     Age,
     Id,
     CtrName,
     Rank,
     Levelid,
     business
)
(
select 
JSON_Value (a.value, '$.CtrName)  CtrName,
JSON_Value (a.value, '$.Rank')  Rank,
JSON_Value (a.value, '$.Levelid')  Levelid,
JSON_Value (a.value, '$.business')  business
 from OPENJSON(@json, '$.easy') as easy1 CROSS APPLY OPENJSON(easy1.value, '$.Additionalinfo') as b
)

标签： sqljsonsql-server

解决方案

“OPENJSON 是一个表值函数，有助于在 SQL Server 中解析 JSON，它以表格式返回 JSON 文本的数据值和类型。现在，我们将看看这个函数的语法。”

OPENJSON( jsonExpression  [, jsonPath ] )
[ WITH (column_mapping_definition1
        [,column_mapping_definition2] 
        [,… column_mapping_definitionN])
]

https://www.sqlshack.com/how-to-parse-json-in-sql-server/

*在建议回答后编辑问题后编辑*

select您在以下第 2 和第 3子句的行中缺少单引号：

JSON_Value (a.value, '$.CtrName)  CtrName,

请更改为以下内容：

JSON_Value (a.value, '$.CtrName')  CtrName,

推荐阅读