如何使用SQL Server解析嵌套的JSON数组 [英] How to parse nested JSON array using SQL Server

问题描述

我目前能够使用SQL Server的"OPENJSON WITH(..."语法来解析大多数JSON文件.但是，此特定文件包含嵌套数组，我不知道该如何处理.

I am currently able to parse most of a JSON file using SQL Server's "OPENJSON WITH (..." syntax. However, this particular file contains nested arrays which I do not know how to handle.

我阅读的许多示例都将JSON作为变量引用.在这种情况下，我正在调用文件:

Many of the examples I read reference JSON as a variable. In this case I am calling a file:

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的1st 5属性以及嵌套的收件人"数组.

One of the attributes contains a nested array. Below I have copied the 1st 5 attributes of my JSON, as well as the nested "recipients" array.

如何构造SQL来分析此部分?

How do I structure my SQL to parse this section?

"doc": {
    "_id": "[e-mail]",
    "_rev": "3-e119db13dae8d50ae0c4579ba9c87fc9",
    "delivery": {
        "application": "App_XYZ",
        "sender": {
            "id": "[e-mail]",
            "type": "user"
        },
        "recipients": [{
                "type": "email",
                "recipient": "\"Artzer, Daniel J\" <[e-mail]>",
                "sentTS": "2017-10-18T13:04:00.133Z"
            },
            {
                "type": "email",
                "recipient": "\"Higgins, Laura L\" <[e-mail]>",
                "sentTS": "2017-10-18T13:04:00.133Z"
            },
            {
                "type": "email",
                "recipient": "\"Friedman, Brian\" <[e-mail]>",
                "sentTS": "2017-10-18T13:04:00.133Z"
            },
            {
                "type": "email",
                "recipient": "\"Garcia, Charlie M\" <[e-mail]>",
                "sentTS": "2017-10-18T13:04:00.133Z"
            }
        ]
    },

推荐答案

我遇到了同样的问题，最后我使用多个CROSS APPLY子句解决了这个问题.

I just had the same problem, and I ended up solving it with multiple CROSS APPLY clauses.

这是我的JSON的示例:

Here's an example of my JSON:

DECLARE @PermsJSON NVARCHAR(MAX) =
N'[{
    "AppId": 1,
    "Perms":
    [{

        "Permission": ["AA", "BB"],
        "PermissionTypeID": 2
    },
    {
        "Permission": ["10"],
        "PermissionTypeID": 1
    }]
},
{
    "AppId": 2,
    "Perms":
    [{

        "Permission": ["IM", "NM"],
        "PermissionTypeID": 2
    },
    {
        "Permission": ["42"],
        "PermissionTypeID": 1
    }]
}]';

然后我可以使用以下查询对其进行解析:

And then I can parse it using the following query:

SELECT
    a.AppId
    ,[Permission] = c.Value
    ,b.PermissionTypeID
FROM
    OPENJSON(@PermsJSON)
    WITH
        (
            AppId INT N'$.AppId'
            ,Perms NVARCHAR(MAX) AS JSON
        ) AS a
CROSS APPLY
    OPENJSON(a.Perms)
    WITH
        (
            PermissionTypeID INT
            ,[Permission] NVARCHAR(MAX) AS JSON
        ) AS b
CROSS APPLY OPENJSON(b.Permission) AS c;

结果如下:

AppId   Permission  PermissionTypeID
1       AA          2
1       BB          2
1       10          1
2       IM          2
2       NM          2
2       42          1

这篇关于如何使用SQL Server解析嵌套的JSON数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！