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

Is this SQL server 2017 & compatibility level 130 or higher? — Pranav Singh
– Pranav Singh, Commented Sep 14, 2018 at 3:23

Gottfried Lesigang · Accepted Answer · 2018-09-14 06:17:22Z

5

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.

answered Sep 14, 2018 at 6:17

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

hypnagogia Over a year ago

This worked for returning the result set but I needed data to be inserted into a table which contains foreign keys so I used Pranav's solution below. I found that removing the wrapper wasn't interfering with the data I was going to be sending in.

Gottfried Lesigang Over a year ago

@hypnagogia You can insert all at once directly from this statement? Typically such things are done against a staging table (2-step-import).

hypnagogia Over a year ago

It actually did work, I was able to insert into the table by using AJAX call multiplexing, I sent a request with an array of objects and got all data in the correct order.

Pranav Singh · Accepted Answer · 2018-09-14 04:07:42Z

2

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.

answered Sep 14, 2018 at 4:07

Pranav Singh

20.3k32 gold badges83 silver badges109 bronze badges

6 Comments

Venkataraman R Over a year ago

you are asking them to change the json file. you have to provide solution with their json file.

Pranav Singh Over a year ago

not changing json but removing redundant wrapper, being a dev we know it is easier & efficient to remove at app layer as Serialization is done one entity having collection of files named files. Just send one level less to db is doing nothing at app level. If code can't be changed i can change answer but it is more efficient this way

Gottfried Lesigang Over a year ago

I must heavily disagree! 1) It is very easy to solve this on query level, see my answer. 2) Very often we cannot change the sending app. 3) Even more often we do not want to change the sending app. 4) We do not want to mix differently styled data 5) and - assumably - there is existing data already and 6) you do not know, if this wrapper is redundant.

Pranav Singh Over a year ago

Fair enough, both are assumptions yet. Depends on how you see, I work end-to-end from ui to code to db so seems more performative to remove redundant wrapper in calling stored procedure(no class changes required obj.files instead of obj) since in long run we have to maintain the mess. Sending only what you require is always done when do performance tuning.

hypnagogia Over a year ago

This worked perfectly! It was much easier to work with the data by removing that wrapper, and it easily added to my table. Thanks!

|

Venkataraman R · Accepted Answer · 2018-09-14 04:21:38Z

2

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

answered Sep 14, 2018 at 4:21

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

1 Comment

Gottfried Lesigang Over a year ago

This might work, but OPENJSON will return a table, quite some overhead for a root element. You might want to check my answer. It's better to use JSON_QUERY and then OPENJSON on this return value.

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related