Looping through array of objects in SQL Server and getting error: JSON text is not properly formatted

Question

I'm trying to loop through an array of objects within a JSON object @files, and insert each object from the array into a table, but I'm getting this error:

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

The JSON was valid using JSONLint, so I know it's not the object that I declared, unless I'm wrong. When selecting the error it highlights this in the OPENJSON WITH() statement:

file_name 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

user_5089204 · Answer

No need for any loops and no need to change the input. this can easily be solved with this query:

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'
);

The return is a simple resultset you can use for any operations:

+---------+----------------+-----------+----------------+--------------------+------------------+---------+--------------------+
| 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 |
+---------+----------------+-----------+----------------+--------------------+------------------+---------+--------------------+

I use JSON_QUERY to get into $.files. OPENJSON will return the array-of-objects, while the WITH clause will transform the object to named and typed columns.

user_704008 · Answer

TRY THIS:

ALTER PROCEDURE files_uploadAll

@document_id INT OUTPUT,
@files NVARCHAR(MAX)



AS
BEGIN

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(@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'
    )
    SET @document_id=SCOPE_IDENTITY()

END

Execution:

DECLARE @d INT
DECLARE @f NVARCHAR(MAX) = N'[
      {  
            "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=@f, @document_id=@d OUTPUT

Here if you notice I have just added array as param & with no loop we can insert data with performance.

user_634935 · Answer

As you are having root element in your JSON, you need to call OPENJSON with root element, as given below. You will not get error now.

Refer to JSON support in SQL Server 2016

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"
       }
    ] '          

SELECT * FROM OPENJSON(@files,'$.files');

Looping through array of objects in SQL Server and getting error: JSON text is not properly formatted

3 Answers