sql - OPENJSON 给出空值
问题描述
我正在使用 SQL Express Edition 2016
这是我的查询。它返回空值。
Declare @JSON varchar(max)
SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'F:\JKPM\18Aug2018\Mod1.json', SINGLE_CLOB) as j
SELECT * FROM OPENJSON (@JSON)
WITH(transactionId nvarchar(50))
我的 JSON 是:
{
"data": {
"deviceTransactions": [{
"transactionId": "3f147089-ff52-5552-a29b-cd32a7e2ba97",
"organizationId": "9023179c-7888-4c5e-a831-28259b8a8872",
"device": "JTLSPERF1",
"event": "PUSH",
"siteName": "TEST-SITE",
"siteId": "5555f068-3ed2-4f83-b084-666f4f92734",
"createdTime": "Sat, 18 Aug 2018 13:51:57 GMT",
"updatedTime": "Sat, 18 Aug 2018 13:53:07 GMT",
"userName": "test1",
"userId": "test1",
"status": "Failed",
"details": {
"count": 251,
"deviceId": "44343570-7eb5-11e8-8160-057818f1057b",
"error": {
"errorMessage": "Failed to start polling status of the Queue",
"stage": "MANAGE_PUSH",
"errorCode": "ERR021001"
},
"jcigId": "4rtr5aa6a2-71b5-455f-8890-7e2aa398b757"
},
"cancelledBy": ""
}],
"libraryTransactions": []
},
"success": true,
"statusCode": 200
}
解决方案
transactionId 是 deviceTransactions 的一部分,它也是一个数组。以下是在这种情况下如何使用 openjson 的示例:
DECLARE @json NVARCHAR(MAX);
SET @json = N'
{
"data": {
"deviceTransactions": [{
"transactionId": "3f147089-ff52-5552-a29b-cd32a7e2ba97",
"organizationId": "9023179c-7888-4c5e-a831-28259b8a8872",
"device": "JTLSPERF1",
"event": "PUSH",
"siteName": "TEST-SITE",
"siteId": "5555f068-3ed2-4f83-b084-666f4f92734",
"createdTime": "Sat, 18 Aug 2018 13:51:57 GMT",
"updatedTime": "Sat, 18 Aug 2018 13:53:07 GMT",
"userName": "test1",
"userId": "test1",
"status": "Failed",
"details": {
"count": 251,
"deviceId": "44343570-7eb5-11e8-8160-057818f1057b",
"error": {
"errorMessage": "Failed to start polling status of the Queue",
"stage": "MANAGE_PUSH",
"errorCode": "ERR021001"
},
"jcigId": "4rtr5aa6a2-71b5-455f-8890-7e2aa398b757"
},
"cancelledBy": ""
}],
"libraryTransactions": []
},
"success": true,
"statusCode": 200
}
';
--This will get you just the transactionId
SELECT *
FROM OPENJSON(@json, '$.data.deviceTransactions')
WITH (
[transactionId] NVARCHAR(200) '$.transactionId')
--This gets you all the columns
SELECT *
FROM OPENJSON(@json, '$.data.deviceTransactions')
WITH (
[transactionId] NVARCHAR(200) '$.transactionId'
, [organizationId] NVARCHAR(200) '$.organizationId'
, [device] NVARCHAR(200) '$.device'
, [event] NVARCHAR(200) '$.event'
, [siteName] NVARCHAR(200) '$.siteName'
, [createdTime] NVARCHAR(200) '$.createdTime'
, [updatedTime] NVARCHAR(200) '$.updatedTime'
, [userName] NVARCHAR(200) '$.userName'
, [userId] NVARCHAR(200) '$.userId'
, [status] NVARCHAR(200) '$.status'
, [count] NVARCHAR(200) '$.details.count'
, [deviceId] NVARCHAR(200) '$.details.deviceId'
, [errorMessage] NVARCHAR(MAX) '$.details.error.errorMessage'
, [errorStage] NVARCHAR(200) '$.details.error.stage'
, [errorCode] NVARCHAR(200) '$.details.error.errorCode'
, [jcigId] NVARCHAR(200) '$.details.jcigId'
, [cancelledBy] NVARCHAR(200) '$.cancelledBy'
)
推荐阅读
- python - 如何构建一个 Python 比较器,以 PostgreSQL 的方式对字符串进行排序?
- java - Spring MVC 轮播
- android - Ionic Cordova 7.1.4 Android 构建失败
- c++ - 如何处理来自另一个线程的 win32 事件?
- python - Python从字符串中删除嵌套的unicode'u'符号
- amazon-web-services - 无法使用 Postman 使用预签名 URL 将文件上传到 S3。错误:签名不匹配
- javascript - 将元素绝对定位在部分底部会添加不需要的第二个滚动条
- c# - 使用多个用户控件从 Mysql 数据库加载大数据的最佳方法
- html - 每行中具有相同高度单元格的表格布局
- php - 通过 PDO fetchAll (PDO::FETCH_OBJ) 获取从 DB 检索到的对象的属性