arrays - 循环遍历 SQL Server 中的对象数组并收到错误 : JSON text is not properly formatted

Question

我正在尝试循环遍历 JSON 对象 @files 中的对象数组，并将数组中的每个对象插入表中，但我收到此错误:

JSON text is not properly formatted. Unexpected character '.' is found at position 0.

使用 JSONLint 时 JSON 是有效的，所以我知道它不是我声明的对象，除非我错了。选择错误时，它会在 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

Answer 1

不需要任何循环，也不需要更改输入。这可以通过以下查询轻松解决:

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 子句会将对象转换为命名和类型列。