arrays - 循环遍历 SQL Server 中的对象数组并出现错误:JSON 文本格式不正确
问题描述
我正在尝试遍历 JSON 对象@files 中的对象数组,并将数组中的每个对象插入到表中,但出现此错误:
JSON 文本格式不正确。意外字符“。” 在位置 0 处找到。
JSON 使用 JSONLint 是有效的,所以我知道它不是我声明的对象,除非我错了。选择错误时,它会在 OPENJSON WITH() 语句中突出显示:
文件名 NVARCHAR(100) '$.fileName',
ALTER PROCEDURE files_uploadAll
@document_id INT OUTPUT,
@files NVARCHAR(MAX)
/*
DECLARE @document_id INT
DECLARE @files NVARCHAR(MAX) = N'{
"files": [
{
"noteId": 1,
"documentTitle": "doc1",
"fileName": "doc1.pdf",
"fileExtension": "pdf",
"mimeType": "application/pdf",
"documentTypeCd": "MSA",
"userId": 1,
"url": "http://www.url.com"
},
{
"noteId": 2,
"documentTitle": "doc2",
"fileName": "doc2.doc",
"fileExtension": "doc",
"mimeType": "application/msword",
"documentTypeCd": "MSA",
"userId": 1,
"url": "http://www.url.com"
}
]
}';
EXECUTE files_uploadAll @files=@files, @document_id=@document_id OUTPUT
*/
AS
DECLARE @filesArray NVARCHAR(MAX)
SET @filesArray = (SELECT '$.files' FROM OPENJSON(@files))
DECLARE @filesList NVARCHAR(MAX), @i int
SELECT @i=0, @filesList = @filesArray
WHILE (@i < LEN(@filesList))
BEGIN
DECLARE @item NVARCHAR(MAX)
SELECT @item = SUBSTRING(@filesList, @i, CHARINDEX(',',@filesList,@i)-@i)
INSERT INTO documents
(note_id, document_title, file_name, file_extension, mime_type, document_type_cd, user_id, url)
SELECT note_id, document_title, file_name, file_extension, mime_type, document_type_cd, user_id, url
FROM OPENJSON(@item)
WITH (
note_id INT '$.noteId',
document_title NVARCHAR(100) '$.documentTitle',
file_name NVARCHAR(100) '$.fileName',
file_extension NVARCHAR(25) '$.fileExtension',
mime_type NVARCHAR(50) '$.mimeType',
document_type_cd CHAR(5) '$.documentTypeCd',
user_id int '$.userId',
url NVARCHAR(1000) '$.url'
)
SET @document_id=SCOPE_IDENTITY()
SET @i = CHARINDEX(',',@filesList,@i)+1
IF(@i = 0) SET @i = LEN(@filesList)
END
解决方案
不需要任何循环,也不需要更改输入。这可以通过以下查询轻松解决:
SELECT *
FROM OPENJSON(JSON_QUERY(@files,'$.files'))
WITH (
note_id INT '$.noteId',
document_title NVARCHAR(100) '$.documentTitle',
file_name NVARCHAR(100) '$.fileName',
file_extension NVARCHAR(25) '$.fileExtension',
mime_type NVARCHAR(50) '$.mimeType',
document_type_cd CHAR(5) '$.documentTypeCd',
user_id int '$.userId',
url NVARCHAR(1000) '$.url'
);
返回是一个简单的结果集,可用于任何操作:
+---------+----------------+-----------+----------------+--------------------+------------------+---------+--------------------+
| note_id | document_title | file_name | file_extension | mime_type | document_type_cd | user_id | url |
+---------+----------------+-----------+----------------+--------------------+------------------+---------+--------------------+
| 1 | doc1 | doc1.pdf | pdf | application/pdf | MSA | 1 | http://www.url.com |
+---------+----------------+-----------+----------------+--------------------+------------------+---------+--------------------+
| 2 | doc2 | doc2.doc | doc | application/msword | MSA | 1 | http://www.url.com |
+---------+----------------+-----------+----------------+--------------------+------------------+---------+--------------------+
我JSON_QUERY
用来进入$.files
. OPENJSON
将返回对象数组,而WITH
子句将对象转换为命名和类型列。
推荐阅读
- node.js - 每次我尝试安装下划线时,它都会给我这个错误:
- snowflake-cloud-data-platform - 雪花到底支持什么SQL方言
- amazon-web-services - DynamoDB 条件 Put_Item
- java - 如何在 OpenApi 3.0.1 中使用枚举属性
- javascript - 将文本元素与“%”符号(如数字)进行比较并查看数字是否大于 0 不起作用
- reactjs - 为 webpack 设置 tinymce
- java - 我在 NETSUITE 中不断收到 Invalid signature 错误
- c++ - Googletest:CLANG 在 GCC 失败的地方编译
- excel - 比较两个 Excel 工作表 – 突出差异 – 宏
- c - 为什么我们甚至需要缓存一致性?