我目前能够使用 SQL Server 解析 JSON 文件的大部分内容OPENJSON WITH (...
句法。但是,这个特定文件包含我不知道如何处理的嵌套数组。
我读到的许多示例都将 JSON 引用为变量。在这种情况下,我调用一个文件:
select DEV_JSON.*
from OPENROWSET
(BULK 'C:\Users\Myuser\Documents\JSON_extract.json', SINGLE_CLOB) as my_datafile
CROSS APPLY OPENJSON(BulkColumn)
WITH
(DOC_ID varchar(100) '$.doc._id',
DOC_REV varchar(45) '$.doc._rev',
DELY_APPL_NAME varchar(20) '$.doc.delivery.application',
DELY_SENT_BY varchar(25) '$.doc.delivery.sender.id',
DELY_SENT_TYPO varchar(20) '$.doc.delivery.sender.type',
.....
....
...
..) as DEV_JSON
其中一个属性包含一个嵌套数组。下面我复制了 JSON 的前 5 个属性,以及嵌套的“recipients”数组。
如何构建 SQL 来解析此部分?
"doc": {
"_id": "[email protected] /cdn-cgi/l/email-protection",
"_rev": "3-e119db13dae8d50ae0c4579ba9c87fc9",
"delivery": {
"application": "App_XYZ",
"sender": {
"id": "[email protected] /cdn-cgi/l/email-protection",
"type": "user"
},
"recipients": [{
"type": "email",
"recipient": "\"Artzer, Daniel J\" <[email protected] /cdn-cgi/l/email-protection>",
"sentTS": "2017-10-18T13:04:00.133Z"
},
{
"type": "email",
"recipient": "\"Higgins, Laura L\" <[email protected] /cdn-cgi/l/email-protection>",
"sentTS": "2017-10-18T13:04:00.133Z"
},
{
"type": "email",
"recipient": "\"Friedman, Brian\" <[email protected] /cdn-cgi/l/email-protection>",
"sentTS": "2017-10-18T13:04:00.133Z"
},
{
"type": "email",
"recipient": "\"Garcia, Charlie M\" <[email protected] /cdn-cgi/l/email-protection>",
"sentTS": "2017-10-18T13:04:00.133Z"
}
]
},