Нужно сгенерировать текст JSON с полем, содержащим массив целых чисел из MSSQL T-SQL.

На чтение 3 мин Опубликовано 24.10.2024

Содержание

Вопрос или проблема
Ответ или решение
Шаг 1: Формирование исходного массива
Шаг 2: Пояснение
Шаг 3: Ожидаемый результат
Шаг 4: Десериализация в C
Заключение

Вопрос или проблема

Я задал ChatGPT этот вопрос: “Как сгенерировать текст JSON вроде этого {“CategoryId”:100,”AccountId”:200,”ItemIds”:[12,13,14,15],”Created”:”2024-10-23″,”SiteId”:111,”PriorityLevel”:2} из MSSQL T-SQL.”

И вот ответ, который я получил:

    SELECT 12 AS ItemId
    UNION ALL
    SELECT 13 AS ItemId
    UNION ALL
    SELECT 14 AS ItemId
    UNION ALL
    SELECT 15 AS ItemId
)
SELECT 
    100 AS CategoryId,
    200 AS AccountId,
    (SELECT ItemId FROM ItemList FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS ItemIds,
    '2024-10-23' AS Created,
    111 AS SiteId,
    2 AS PriorityLevel
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Он сказал, что это даст результат, который я хотел, как показано ниже:

    "CategoryId": 100,
    "AccountId": 200,
    "ItemIds": [12, 13, 14, 15],
    "Created": "2024-10-23",
    "SiteId": 111,
    "PriorityLevel": 2
}

Но на самом деле это было то, что я получил, когда выполнил тестовый запрос:

{"CategoryId":100,"AccountId":200,"ItemIds":"{\"ItemId\":12},{\"ItemId\":13},{\"ItemId\":14},{\"ItemId\":15}","Created":"2024-10-23","SiteId":111,"PriorityLevel":2}

Как сделать так, чтобы ItemIds был "ItemIds": [12, 13, 14, 15], а не "ItemIds":"{\"ItemId\":12},{\"ItemId\":13},{\"ItemId\":14},{\"ItemId\":15}, что вызвало исключение десериализатора, когда я пытался десериализовать сообщение JSON в класс, который ожидает ItemIds как int[] (C#)?

Ответ или решение

Для генерации корректного JSON, который содержит массив целых чисел (int array) в T-SQL Microsoft SQL Server, необходимо использовать правильный подход при формировании подзапроса с использованием конструкции FOR JSON. Проблема, с которой вы столкнулись, заключается в том, что ваш текущий запрос создает вложенный JSON, вместо ожидаемого формата массива.

Давайте разобьем проблему на составные части и детально рассмотрим, как достичь желаемого результата.

Шаг 1: Формирование исходного массива

Для начала, нужно создать временную таблицу (или использовать Common Table Expression – CTE), чтобы сформировать ваш массив значений ItemId. Это позволит вам затем корректно использовать этот набор данных в JSON.

WITH ItemList AS (
    SELECT 12 AS ItemId
    UNION ALL
    SELECT 13 AS ItemId
    UNION ALL
    SELECT 14 AS ItemId
    UNION ALL
    SELECT 15 AS ItemId
)

-- Основной запрос
SELECT 
    100 AS CategoryId,
    200 AS AccountId,
    (SELECT ItemId FROM ItemList FOR JSON PATH) AS ItemIds, -- Здесь мы убираем WITHOUT_ARRAY_WRAPPER
    '2024-10-23' AS Created,
    111 AS SiteId,
    2 AS PriorityLevel
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Шаг 2: Пояснение

В этом запросе:

Создан CTE ItemList, который содержит все значения ItemId, которые вы хотите включить.
Внутренний запрос (SELECT ItemId FROM ItemList FOR JSON PATH) генерирует JSON, который теперь будет представлен в виде массива.
Мы убрали опцию WITHOUT_ARRAY_WRAPPER, так как нам нужно, чтобы SQL Server понимает, что мы хотим вернуть массив JSON, а не одиночный объект.

Шаг 3: Ожидаемый результат

При выполнении этого запроса полученный JSON будет иметь следующую структуру:

{
    "CategoryId": 100,
    "AccountId": 200,
    "ItemIds": [12, 13, 14, 15],
    "Created": "2024-10-23",
    "SiteId": 111,
    "PriorityLevel": 2
}

Шаг 4: Десериализация в C

С приведенным выше решением, вы сможете успешно десериализовать это сообщение JSON в C# класс с массивом типа int[] для ItemIds без возникновения исключений.

Заключение

Используя указанный подход, вы сможете корректно формировать JSON с массивом целых чисел из MSSQL T-SQL. Обратите внимание на ключевые моменты, такие как правильное использование конструкции FOR JSON и формирование массива с помощью запроса. Эти нюансы критически важны для достижения желаемого результата.