我们如何使用 SQL 将 JSON 语句转换为不同的表？例如我们有 JSON：
{"table1":
{"Name":"table1","Items":
[{"Id":1,"FirstName":"John",
"LastName":"Wen","Country":"UK",
"PostCode":1234,"Status":false,
"Date":"2018-09-18T08:30:32.91",}]},
"table2":
{"Name":"table2","Items":
[{"Id":1,"Name":"leo",
"StudentId":102,"CreatedDate":"2018-09-18","Location":"USA"}]}}
在关系型数据库中，一旦 JSON 被转换，我们将得到两个表 例如，模式 'Table1'：
Id FirstName LastName Country PostCode Status Date
1 John Wen UK 1234 false 2018-09-18T08:30:32.91
'Table2' 将如下所示：
Id Name StudentId CreateDate Location
1 Leo 102 2018-9-18 USA
任何人都可以请给任何建议。
您可以使用 openjson 和 json_value 函数执行此操作。请尝试以下操作：
Declare @json nvarchar(max),@table1Items nvarchar(max), @table2Items nvarchar(max)
set @json='{
"table1": {
"Name": "table1",
"Items": [{
"Id": 1,
"FirstName": "John",
"LastName": "Wen",
"Country": "UK",
"PostCode": 1234,
"Status": false,
"Date": "2018-09-18T08:30:32.91"
}, {
"Id": 2,
"FirstName": "John1",
"LastName": "Wen1",
"Country": "UK1",
"PostCode": 12341,
"Status": true,
"Date": "2018-09-15T08:30:32.91"
}]
},
"table2": {
"Name": "table2",
"Items": [{
"Id": 1,
"Name": "leo",
"StudentId": 102,
"CreatedDate": "2018-09-18",
"Location": "USA"
}]
}
}'
set @table1Items=(select value from OpenJSON((select value from OpenJSON(@Json) where [key]='table1')) where [key]='Items')
set @table2Items=(select value from OpenJSON((select value from OpenJSON(@Json) where [key]='table2')) where [key]='Items')
--select for table 1
select JSON_VALUE(val,'$.Id') as ID,
JSON_VALUE(val,'$.FirstName') as FirstName,
JSON_VALUE(val,'$.LastName') as LastName,
JSON_VALUE(val,'$.Country') as Country,
JSON_VALUE(val,'$.PostCode') as PostCode,
JSON_VALUE(val,'$.Status') as Status,
JSON_VALUE(val,'$.Date') as Date
from
(
select value as val from openJSON(@table1Items)
) AS Table1JSON
--select for table 1
select JSON_VALUE(val,'$.Id') as ID,
JSON_VALUE(val,'$.Name') as FirstName,
JSON_VALUE(val,'$.StudentId') as LastName,
JSON_VALUE(val,'$.CreatedDate') as Country,
JSON_VALUE(val,'$.Location') as PostCode
from
(
select value as val from openJSON(@table2Items)
) AS Table2JSON
它完全按照您的意愿工作。最后，两个 select 语句返回您提到的表。只需使用 将它们添加到您想要的表中insert into select。我还尝试将另一个对象添加到 table1 数组并验证它工作正常，即为两个对象返回两行。希望这可以帮助
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句