json - 将任何 JSON 读入 SQL Server 中的键值对列表(EAV 格式)
问题描述
寻找一种在不了解 SQL Server 的 JSON 方法内部结构的情况下读取任何 JSON 的方法,我想出了一个我想分享的方法。
这是提出这个问题的问题。
问题是:如何将未知的 JSON 转换为结构化的 EAV 格式,同时保留有关排序顺序和嵌套级别的所有信息。
理想的输出应该携带原始行的 id 作为实体,Json 的键和值作为属性和值,以及排序列表中特定对象的 JsonPath。
找到嵌入到我的自我回答中的 MCVE(来自链接问题的示例数据)。
解决方案
首先,我们创建一个声明的表变量并用一些示例 JSON 填充它以模拟问题(我在示例中添加了一些数组以反映数组的 JSON 路径):
DECLARE @table TABLE(ID INT IDENTITY, AnyJSON NVARCHAR(MAX));
INSERT INTO @table VALUES
(N' {
"correlationId": "c3xOeEEQQCCA9sEx7-u6FA",
"eventCreateTime": "2020-05-12T15:38:23.717Z",
"time": 1589297903717,
"owner": {
"ownergeography": {
"city": "abc",
"country": "abc"
},
"ownername": {
"firstname": "abc",
"lastname": "def"
},
"clientApiKey": "xxxxx",
"businessProfileApiKey": null,
"userId": null
},
"campaignType": "Mobile push"
}')
,(N'[{
"correlationIds": [
{
"campaignId": [1,2,3],
"correlationId": [{"a":"b"},{"c":"d"},{"e":"f"}]
}
],
"variantId": 1278915,
"utmCampaign": "",
"ua.os.major": "8"
}
,{
"correlationIds": [
{
"campaignId": [1,2,3],
"correlationId": [{"a":"b"},{"c":"d"},{"e":"f"}]
}
],
"variantId": 1278915,
"utmCampaign": "",
"ua.os.major": "8"
}]')
,(N'{
"correlationId": "ls7XmuuiThWzktUeewqgWg",
"eventCreateTime": "2020-05-12T12:40:20.786Z",
"time": 1589287220786,
"modifiedBy": {
"clientId": null,
"clientApiKey": "xxx",
"businessProfileApiKey": null,
"userId": null
},
"campaignType": "Mobile push"
}');
--查询
WITH recCTE AS
(
SELECT ID
,NestLevel = 0
,ObjectIndex = CAST(1 AS bigint)
,SortString = CAST(N'sort' COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
,JsonPath = CAST(N'$' COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
,JsonKey = CAST(N'$' COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
,JsonValue = CAST(AnyJSON COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
,JsonType = CAST(CASE WHEN LEFT(TRIM(AnyJSON),1)=N'[' THEN 4 ELSE 0 END AS TINYINT)
,NestedJSON = CAST(CASE WHEN ISJSON(AnyJSON)=1
THEN AnyJSON
ELSE NULL END COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
FROM @table t
UNION ALL
SELECT r.ID
,r.NestLevel+1
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
,CAST(CONCAT(r.SortString,REPLACE(STR(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),5),' ','0')) COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
,CAST(CONCAT(r.JsonPath, CASE WHEN r.JsonType=4 --<-- see the docs for OPENJSON()
THEN CONCAT('[',A.[key],']')
ELSE '.' + A.[key] END) COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
,CAST(A.[key] COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
,CAST(r.JsonValue COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
,A.[type]
,CAST(A.[value] COLLATE DATABASE_DEFAULT AS NVARCHAR(MAX))
FROM recCTE r
CROSS APPLY OPENJSON(r.NestedJSON) A
WHERE ISJSON(r.NestedJSON)=1
)
SELECT ID
,NestLevel
,ObjectIndex
,JsonPath
,JsonKey
,NestedJSON AS JsonValue
,SortString --<-- just to illustrate the sorting, not needed in the output
FROM recCTE
WHERE ISJSON(NestedJSON)=0
ORDER BY ID,SortString;
结果
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| ID | JsonPath | JsonKey | JsonValue | SortString |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 1 | $.correlationId | correlationId | c3xOeEEQQCCA9sEx7-u6FA | 0 1 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 1 | $.eventCreateTime | eventCreateTime | 2020-05-12T15:38:23.717Z | 0 2 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 1 | $.time | time | 1589297903717 | 0 3 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 1 | $.owner.ownergeography.city | city | abc | 0 4 1 1 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 1 | $.owner.ownergeography.country | country | abc | 0 4 1 2 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 1 | $.owner.ownername.firstname | firstname | abc | 0 4 2 1 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 1 | $.owner.ownername.lastname | lastname | def | 0 4 2 2 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 1 | $.owner.clientApiKey | clientApiKey | xxxxx | 0 4 3 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 1 | $.campaignType | campaignType | Mobile push | 0 5 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[0].correlationIds[0].campaignId[0] | 0 | 1 | 0 1 1 1 1 1 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[0].correlationIds[0].campaignId[1] | 1 | 2 | 0 1 1 1 1 2 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[0].correlationIds[0].campaignId[2] | 2 | 3 | 0 1 1 1 1 3 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[0].correlationIds[0].correlationId[0].a | a | b | 0 1 1 1 2 1 1 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[0].correlationIds[0].correlationId[1].c | c | d | 0 1 1 1 2 2 1 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[0].correlationIds[0].correlationId[2].e | e | f | 0 1 1 1 2 3 1 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[0].variantId | variantId | 1278915 | 0 1 2 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[0].utmCampaign | utmCampaign | | 0 1 3 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[0].ua.os.major | ua.os.major | 8 | 0 1 4 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[1].correlationIds[0].campaignId[0] | 0 | 1 | 0 2 1 1 1 1 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[1].correlationIds[0].campaignId[1] | 1 | 2 | 0 2 1 1 1 2 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[1].correlationIds[0].campaignId[2] | 2 | 3 | 0 2 1 1 1 3 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[1].correlationIds[0].correlationId[0].a | a | b | 0 2 1 1 2 1 1 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[1].correlationIds[0].correlationId[1].c | c | d | 0 2 1 1 2 2 1 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[1].correlationIds[0].correlationId[2].e | e | f | 0 2 1 1 2 3 1 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[1].variantId | variantId | 1278915 | 0 2 2 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[1].utmCampaign | utmCampaign | | 0 2 3 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 2 | $[1].ua.os.major | ua.os.major | 8 | 0 2 4 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 3 | $.correlationId | correlationId | ls7XmuuiThWzktUeewqgWg | 0 1 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 3 | $.eventCreateTime | eventCreateTime | 2020-05-12T12:40:20.786Z | 0 2 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 3 | $.time | time | 1589287220786 | 0 3 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 3 | $.modifiedBy.clientApiKey | clientApiKey | xxx | 0 4 2 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
| 3 | $.campaignType | campaignType | Mobile push | 0 5 |
+----+-------------------------------------------+-----------------+--------------------------+---------------------------------+
简而言之:
- 我们使用递归 CTE 来解决这个问题。
- 该查询将测试任何片段(
[value]
来自OPENJSON
)是否为有效 JSON。 - 如果片段是有效的,这会越来越深入。
- 需要该列
SortString
才能获得最终的排序顺序。 - 和有助于避免数据类型不匹配
CAST()
。COLLATE
递归 CTE 对此非常挑剔......
提示:如果您处理更大的 JSON,您可能需要OPTION (MAXRECURSION 0)
在查询结束时进行设置。
享受 :-)
XML 类似的东西
这是关于如何读取未知 XML 的类似答案。
推荐阅读
- hyperledger-fabric - 主题备用名称的 fabric-ca-client 命令行选项
- python-3.x - if 和 when try/except 语句是矫枉过正的
- node.js - afterAll 中的 Promise 调用未执行
- ios - 来自外部脚踏开关的 Cordova App keydown 事件在 iOS 上不起作用
- sql - 数据库为 SQL Server 前端为 Angular 的高效分页
- jquery - DataTable分页页面选择颜色
- flutter - 如何使用具有所需大小属性的子小部件填充列中的整个空闲位置?
- javascript - Javascript 检查是否单击了 href
- python - 如何删除重复条目但保留第一行选定列值和最后一行选定列值?
- python - 如何在 Flask 中将字典传递给 url_for