Цикл через массив объектов в SQL Server и получение ошибки: текст JSON неправильно отформатирован

Я пытаюсь перебрать массив объектов в объекте JSON @files, и вставляю каждый объект из массива в таблицу, но получаю такую ошибку:

JSON text is not properly formatted. Unexpected character '.' is found at position 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

Это SQL Server 2017 и уровень совместимости 130 или выше?

— 14.09.2018 05:23

arrays json sql-server tsql ssms

13.09.2018 23:44

Структурированный массив Numpy

Однако в реальных проектах я чаще всего имею дело со списками, состоящими из нескольких типов данных. Как мы можем использовать массивы numpy, чтобы...

3 эффективных способа найти максимальное значение в массивах JavaScript

T - 1Bits: Генерация последовательного массива

По мере того, как мы пишем все больше кода, мы привыкаем к определенным способам действий. То тут, то там мы находим код, который заставляет нас...

Что такое деструктуризация массива в JavaScript?

Деструктуризация позволяет распаковывать значения из массивов и добавлять их в отдельные переменные.

5 935

Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Ответ принят как подходящий

ПОПРОБУЙ ЭТО:

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

Исполнение:

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

Здесь, если вы заметили, я только что добавил массив как параметр, и без цикла мы можем вставлять данные с производительностью.

вы просите их изменить файл json. вы должны предоставить решение с их файлом json.

— 14.09.2018 06:19

не изменяя json, но удаляя избыточную оболочку, будучи разработчиком, мы знаем, что его проще и эффективнее удалить на уровне приложения, поскольку сериализация выполняется одним объектом, имеющим коллекцию файлов с именем files. Просто отправьте на один уровень меньше, чтобы db ничего не делал на уровне приложения. Если код не может быть изменен, я могу изменить ответ, но это более эффективно

— 14.09.2018 06:25

Я категорически не согласен! 1) Это очень легко решить на уровне запроса, см. Мой ответ. 2) Очень часто мы не можем изменить приложение-отправитель. 3) Еще чаще мы не хотим менять приложение-отправитель. 4) Мы не хотим смешивать данные с разными стилями 5) и - предположительно - уже существующие данные и 6) вы не знаете, является ли эта оболочка избыточный.

— 14.09.2018 08:24

Честно говоря, оба предположения пока что. В зависимости от того, как вы видите, я работаю от ui до кода к db, поэтому кажется более производительным для оболочки remove redundant при вызове хранимой процедуры (изменения классов не требуются, obj.files вместо obj), поскольку в долгосрочной перспективе мы должны поддерживать беспорядок . Отправка только того, что вам нужно, всегда выполняется при настройке производительности.

— 14.09.2018 08:35

Это сработало отлично! Было намного проще работать с данными, удалив эту оболочку, и ее легко добавить в мою таблицу. Спасибо!

— 14.09.2018 17:18

@hypnagogia, я рада, что это сработало для вас. Вы всегда можете проголосовать за или отметить как ответ, если это поможет

— 15.09.2018 09:03

14.09.2018 06:07

Поскольку у вас есть корневой элемент в вашем JSON, вам необходимо вызвать OPENJSON с корневым элементом, как указано ниже. Теперь вы не получите ошибки.

Обратитесь к поддержке JSON в 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');

Это могло бы сработать, но OPENJSON вернет таблицу, и некоторые накладные расходы для корневого элемента. Вы можете проверить мой ответ. Лучше использовать JSON_QUERY, а затем OPENJSON для этого возвращаемого значения.

— 14.09.2018 08:21

14.09.2018 06:21

Нет необходимости в каких-либо циклах и нет необходимости изменять вход. это можно легко решить с помощью этого запроса:

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 преобразует объект в именованные и типизированные столбцы.

Это сработало для возврата набора результатов, но мне нужно было вставить данные в таблицу, содержащую внешние ключи, поэтому я использовал решение Pranav ниже. Я обнаружил, что удаление оболочки не влияет на данные, которые я собирался отправлять.

— 14.09.2018 17:20

@hypnagogia Можно сразу все вставить прямо из этого утверждения? Обычно такие действия выполняются с использованием промежуточной таблицы (двухэтапный импорт).

— 14.09.2018 17:34

Это действительно сработало, я смог вставить в таблицу с помощью мультиплексирования вызовов AJAX, я отправил запрос с массивом объектов и получил все данные в правильном порядке.

— 17.09.2018 23:20

14.09.2018 08:17