Данные JSON в SQL Server

Относится к: SQL Server 2016 (13.x) и более поздним версиямAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL база данных в Microsoft Fabric

В этой статье представлен обзор формата текстовых данных JSON в SQL Server, Базе данных SQL Azure, Управляемом экземпляре SQL Azure, Azure Synapse Analytics и базе данных SQL в Microsoft Fabric.

Note

Для поддержки JSON требуется уровень совместимости базы данных 130 или более поздней версии.

Overview

JSON — это популярный формат текстовых данных, который используется для обмена данными в современных веб- и мобильных приложениях. Кроме того, JSON используется для хранения неструктурированных данных в файлах журналов или базах данных NoSQL, таких как Microsoft Azure Cosmos DB. Многие веб-службы REST возвращают результаты в формате текста JSON или принимают данные в формате JSON. Например, большинство служб Azure, таких как поиск Azure, служба хранилища Azure и Azure Cosmos DB, имеют конечные точки REST, которые возвращают или принимают JSON. JSON — это также основной формат обмена данными между веб-страницами и веб-серверами с помощью вызовов AJAX.

Функции JSON, впервые появившиеся в SQL Server 2016 (13.x), позволяют объединить понятия NoSQL и реляционные понятия в одной базе данных. Можно объединить классические реляционные столбцы со столбцами, содержащими документы, форматированные как текст JSON в той же таблице, анализировать и импортировать документы JSON в реляционных структурах или форматировать реляционные данные в текст JSON.

Ниже приведен пример текста JSON:

[
    {
        "name": "John",
        "skills": [ "SQL", "C#", "Azure" ]
    },
    {
        "name": "Jane",
        "surname": "Doe"
    }
]

С помощью встроенных функций и операторов SQL Server вы можете выполнять указанные ниже действия с текстом JSON.

• Синтаксический анализ текста JSON, а также считывание и изменение значений.
• Преобразования массивов объектов JSON в табличный формат.
• Выполнение любого запроса Transact SQL к преобразованным объектам JSON.
• Форматирование результатов запросов Transact-SQL в формате JSON.

Изменения SQL Server 2025

SQL Server 2025 (17.x) представляет следующие улучшения JSON, все в настоящее время в предварительной версии:

• Изменение метода для типа JSON
• СОЗДАНИЕ ИНДЕКСА JSON
• JSON_CONTAINS
• Поддержка подстановочных знаков в массивах выражений пути ANSI SQL
• Конструкция ANSI SQL WITH ARRAY WRAPPER в функции JSON_QUERY

Основные возможности JSON

В следующих разделах описываются основные возможности, предоставляемые SQL Server со встроенной поддержкой JSON.

Тип данных JSON

Note

Тип данных JSON:

• общедоступен для Базы данных SQL Azure и Управляемого экземпляра SQL Azure с SQL Server 2025 или политикой всегда актуальных обновлений.
• находится в предварительной версии для SQL Server 2025 (17.x) и базы данных SQL в Fabric.

Новый тип данных json, который позволяет хранить документы JSON в собственном двоичном формате, обеспечивает следующие преимущества по сравнению с хранением данных JSON в varchar/nvarchar:

• Более эффективные операции чтения, так как документ уже анализируется
• Более эффективные операции записи, так как запрос может обновлять отдельные значения без доступа ко всему документу
• Более эффективное хранилище, оптимизированное для сжатия
• Отсутствие изменений в совместимости с существующим кодом

Использование функций JSON, описанных в этой статье, остается наиболее эффективным способом запроса типа данных JSON . Дополнительные сведения о собственном типе данных JSON см. в разделе "Тип данных JSON".

Извлечение значений из текста JSON и их использование в запросах

Если у вас есть текст JSON, который хранится в таблицах базы данных, вы можете прочитать или изменить значения в тексте JSON с помощью следующих встроенных функций.

• ISJSON проверяет, содержит ли строка допустимый JSON.
• JSON_VALUE извлекает скалярное значение из строки JSON.
• JSON_QUERY извлекает объект или массив из строки JSON.
• JSON_MODIFY изменяет значение в строке JSON.

Example

В следующем примере запрос использует реляционные и JSON-данные (хранящиеся в столбце с именем jsonCol) из таблицы с именем People:

SELECT Name,
       Surname,
       JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,
       JSON_VALUE(jsonCol, '$.info.address."Address Line 1"') + ' ' +
           JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
       JSON_QUERY(jsonCol, '$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol) > 0
      AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
      AND STATUS = 'Active'
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode');

Приложения и средства не видят разницы между значениями, взятыми из скалярных столбцов таблицы, и значениями, взятыми из столбца JSON. Значения из текста JSON можно использовать в любой части запроса Transact-SQL (включая предложения WHERE, ORDER BY или GROUP BY, агрегатные операции с окнами и т. д.). Для ссылок на значения в тексте JSON функции JSON используют синтаксис типа JavaScript.

Дополнительные сведения см. в статье "Проверка, запрос и изменение данных JSON" со встроенными функциями, JSON_VALUE и JSON_QUERY.

Изменение значений JSON

Если необходимо изменить части текста JSON, можно использовать функцию JSON_MODIFY для обновления значения свойства в строке JSON и возврата обновленной строки JSON. В следующем примере показано, как изменить значение свойства в переменной, которая содержит данные в формате JSON.

DECLARE @json AS NVARCHAR (MAX);

SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');

SELECT @json AS modifiedJson;

Вот результирующий набор.

{"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}}

Преобразование коллекций JSON в набор строк

Для выполнения запросов JSON в SQL Server никакой особый язык запросов не требуется. Для запроса данных JSON можно использовать стандартные инструкции T-SQL. Если необходимо создать запрос или отчет по данным JSON, можно легко преобразовать данные JSON в строки и столбцы, вызвав OPENJSON функцию набора строк. Дополнительные сведения см. в разделе "Анализ и преобразование данных JSON с помощью OPENJSON".

Следующий пример вызывает OPENJSON и преобразует массив объектов, хранящихся в переменной, в @json набор строк, который можно запросить с помощью стандартной инструкции Transact-SQL SELECT :

DECLARE @json AS NVARCHAR (MAX);

SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON (@json) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR (50) '$.info.name',
    lastName NVARCHAR (50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
);

Вот результирующий набор.

ID	firstName	lastName	age	dateOfBirth
2	John	Smith	25
5	Jane	Smith		2005-11-04T12:00:00

OPENJSON преобразует массив объектов JSON в таблицу, в которой каждый объект представлен как одна строка, а пары "ключ-значение" возвращаются в виде ячеек. К выходным данным применяются следующие правила.

• OPENJSON преобразует значения JSON в типы, указанные в предложении WITH .
• OPENJSON может обрабатывать как пары неструктурированных ключей и значений, так и вложенные, иерархически упорядоченные объекты.
• Все поля в тексте JSON возвращать необязательно.
• Если значения JSON не существуют, OPENJSON возвращает NULL значения.
• Путь, обозначенный после указания типа, можно использовать для ссылки на вложенное свойство или просто для ссылки на свойство с другим именем.
• Необязательный strict префикс в пути указывает, что значения для указанных свойств должны существовать в тексте JSON.

Дополнительные сведения см. в разделе "Анализ и преобразование данных JSON с помощью OPENJSON иOPENJSON".

Документы JSON могут содержать вложенные элементы и иерархические данные, которые нельзя сопоставить напрямую с стандартными реляционными столбцами. В этом случае можно выполнить сведение иерархии JSON посредством соединения родительской сущности с вложенными массивами.

В следующем примере второй объект в массиве содержит вложенный массив, представляющий навыки сотрудника. Каждый вложенный объект может быть проанализирован с помощью дополнительных вызовов функции OPENJSON:

DECLARE @json AS NVARCHAR (MAX);

SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"}
]';

SELECT id,
       firstName,
       lastName,
       age,
       dateOfBirth,
       skill
FROM OPENJSON (@json) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR (50) '$.info.name',
    lastName NVARCHAR (50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob',
    skills NVARCHAR (MAX) '$.info.skills' AS JSON
)
OUTER APPLY OPENJSON (skills) WITH (skill NVARCHAR (8) '$');

Массив skills возвращается в первом OPENJSON виде исходного фрагмента текста JSON и передается в другую OPENJSON функцию с помощью APPLY оператора. Вторая OPENJSON функция анализирует массив JSON и возвращает строковые значения в виде набора строк одного столбца, который будет присоединен к результату первого OPENJSON.

Вот результирующий набор.

ID	firstName	lastName	age	dateOfBirth	skill
2	John	Smith	25
5	Jane	Smith		2005-11-04T12:00:00	SQL
5	Jane	Smith		2005-11-04T12:00:00	C#
5	Jane	Smith		2005-11-04T12:00:00	Azure

OUTER APPLY OPENJSON объединяет сущность первого уровня с вложенным массивом и возвращает неструктурированный набор результатов. Из-за JOIN вторая строка повторяется для каждого навыка.

Преобразование данных SQL Server в JSON или экспортирование JSON

Note

Преобразование данных Azure Synapse Analytics в JSON или экспорт JSON не поддерживается.

Форматирование данных SQL Server или результатов запросов SQL в формате JSON путем добавления FOR JSON предложения в инструкцию SELECT . Используйте FOR JSON для делегирования форматирования выходных данных JSON из клиентских приложений в SQL Server. Дополнительные сведения см. в разделе "Формат результатов запроса в формате JSON" с помощью FOR JSON.

В следующем примере используется режим PATH с предложением FOR JSON :

SELECT id,
       firstName AS "info.name",
       lastName AS "info.surname",
       age,
       dateOfBirth AS dob
FROM People
FOR JSON PATH;

Предложение FOR JSON форматирует результаты SQL как текст JSON, который можно предоставить любому приложению, которое понимает JSON. Параметр PATH содержит псевдонимы, разделенные точками, в предложении SELECT для вложения объектов в результаты запросов.

Вот результирующий набор.

[
  {
    "id": 2,
    "info": {
      "name": "John",
      "surname": "Smith"
    },
    "age": 25
  },
  {
    "id": 5,
    "info": {
      "name": "Jane",
      "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
  }
]

Дополнительные сведения см. в разделе Форматирование результатов запроса в формате JSON с предложением FOR JSON и SELECT — FOR.

Данные JSON из агрегатов

Агрегатные функции JSON позволяют создавать объекты ИЛИ массивы JSON на основе агрегата из данных SQL.

• JSON_OBJECTAGG создает объект JSON из агрегирования данных ИЛИ столбцов SQL.
• JSON_ARRAYAGG создает JSON массив из агрегированных данных или столбцов SQL.

Note

JSON_OBJECTAGG и JSON_ARRAYAGG агрегатные функции обычно доступны для базы данных SQL Azure, управляемого экземпляра SQL Azure (с SQL Server 2025 или Always-up-to-dateполитикой обновления), а также хранилища данных Fabric и в режиме предварительного просмотра для SQL Server 2025 (17.x).

Варианты использования данных JSON в SQL Server

Поддержка JSON в SQL Server и базе данных SQL Azure позволяет объединить принципы NoSQL и реляционных баз данных. Вы можете легко преобразовывать реляционные данные в частично структурированные и наоборот. JSON не является заменой существующих реляционных моделей, однако. Ниже приведены некоторые конкретные варианты использования с преимуществами поддержки JSON в SQL Server и базе данных SQL.

• Упрощение сложных моделей данных

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

• Хранение данных розничной торговли и электронной коммерции

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

• Обработка данных журнала и данных телеметрии

  Загружайте, запрашивайте и анализируйте данные журнала, хранящиеся в виде JSON-файлов, используя все возможности языка Transact-SQL.

• Сохранение частично структурированных данных Интернета вещей

  Чтобы проанализировать данные Интернета вещей в режиме реального времени, загружайте входящие данные непосредственно в базу данных, а не размещайте их в месте хранения.

• Упрощение разработки REST API

  Легко преобразовывайте реляционные данных из базы данных в формат JSON, используемый интерфейсами REST API, которые поддерживают ваш веб-сайт.

Объединение реляционных данных и данных JSON

SQL Server предоставляет гибридную модель для хранения и обработки реляционных данных и данных JSON с использованием стандартного языка Transact-SQL. Вы можете формировать коллекции документов JSON в таблицах, устанавливать отношения между ними, комбинировать строго типизированные скалярные столбцы, которые хранятся в таблицах с гибкими парами "ключ —значение", хранящимися в столбцах JSON, и запрашивать скалярные значения и значения JSON в одной таблице или нескольких с использованием полного Transact-SQL.

Текст JSON хранится в столбцах varchar или nvarchar и индексируется как обычный текст. Любая функция или компонент SQL Server, которые поддерживают текст, поддерживают и JSON, поэтому в обмене данных между JSON и другими компонентами SQL Server нет практически никаких ограничений. JSON можно хранить во временных таблицах или в таблицах в памяти, применять к тексту JSON предикаты безопасности на уровне строк и т. д.

Ниже приведены некоторые варианты использования, показывающие, как использовать встроенную поддержку JSON в SQL Server.

Хранение и индексирование данных JSON в SQL Server

JSON — это текстовый формат, поэтому документы JSON можно хранить в столбцах nvarchar в базе данных SQL. Так как тип nvarchar поддерживается во всех подсистемах SQL Server, можно поместить документы JSON в таблицы с кластеризованными индексами columnstore, оптимизированными для памяти таблицами или внешними файлами, которые можно считывать с помощью OPENROWSET или PolyBase.

Дополнительные сведения о возможностях хранения, индексирования и оптимизации данных JSON в SQL Server, см. в следующих статьях.

• Хранение документов JSON
• Индексирование данных JSON
• Оптимизация обработки JSON с помощью выполняющейся в памяти OLTP

Загрузка файлов JSON в SQL Server

Сведения, которые хранятся в файлах, можно отформатировать как стандартный JSON или JSON с разбивкой на строки. SQL Server может импортировать содержимое JSON-файлов, проанализировать его с помощью OPENJSON или JSON_VALUE функций и загрузить его в таблицы.

• Если документы JSON хранятся в локальных файлах, на общих сетевых дисках или в хранилище файлов Azure, доступном для SQL Server, данные JSON можно загрузить в SQL Server с помощью массового импорта.

• Если файлы JSON с разбивкой на строки хранятся в хранилище BLOB-объектов Azure или в файловой системе Hadoop, вы можете загрузить текст JSON с помощью Polybase, проанализировать его в коде Transact-SQL и загрузить в таблицы.

Импорт данных JSON в таблицы SQL Server

Если необходимо загрузить данные JSON из внешней службы в SQL Server, можно использовать OPENJSON для импорта данных в SQL Server вместо анализа данных на уровне приложения.

На поддерживаемых платформах используйте собственный тип данных JSON вместо nvarchar(max) для повышения производительности и более эффективного хранилища.

DECLARE @jsonVariable AS NVARCHAR (MAX);

SET @jsonVariable = N'[
  {
    "Order": {
      "Number":"SO43659",
      "Date":"2011-05-31T00:00:00"
    },
    "AccountNumber":"AW29825",
    "Item": {
      "Price":2024.9940,
      "Quantity":1
    }
  },
  {
    "Order": {
      "Number":"SO43661",
      "Date":"2011-06-01T00:00:00"
    },
    "AccountNumber":"AW73565",
    "Item": {
      "Price":2024.9940,
      "Quantity":3
    }
  }
]';

-- INSERT INTO <sampleTable>
SELECT SalesOrderJsonData.*
FROM OPENJSON (@jsonVariable, N'$') WITH (
    Number VARCHAR (200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR (200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData;

Вы можете предоставить содержимое переменной JSON из внешней службы REST, отправить его в виде параметра из платформы JavaScript на стороне клиента или загрузить из внешних файлов. Результаты можно легко вставить, обновить или объединить из текста JSON в таблицу SQL Server.

Анализ данных JSON с помощью запросов SQL

Если необходимо отфильтровать или агрегировать данные JSON для создания отчетов, можно использовать OPENJSON для преобразования JSON в реляционный формат. После этого подготовьте отчеты, используя стандартный язык Transact-SQL и встроенные функции.

SELECT Tab.Id,
       SalesOrderJsonData.Customer,
       SalesOrderJsonData.Date
FROM SalesOrderRecord AS Tab
CROSS APPLY OPENJSON (Tab.json, N'$.Orders.OrdersArray') WITH (
    Number VARCHAR (200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR (200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'
ORDER BY JSON_VALUE(Tab.json, '$.Group'),
         Tab.DateModified;

В одном и том же запросе можно использовать стандартные столбцы таблицы и значения из текста JSON. Для повышения эффективности запроса можно добавить индексы в выражение JSON_VALUE(Tab.json, '$.Status'). Дополнительные сведения см. в разделе Индексирование данных JSON.

Возврат данных из таблицы SQL Server в формате JSON

Если у вас есть веб-служба, которая получает данные с уровня базы данных и возвращает их в формате JSON, либо платформы или библиотеки JavaScript, которые принимают данные в формате JSON, вы можете отформатировать выходные данные JSON прямо в запросе SQL. Вместо написания кода или включения библиотеки для преобразования результатов табличного запроса и сериализации объектов в формат JSON можно делегировать FOR JSON форматирование JSON в SQL Server.

Например, можно сформировать выходные данные JSON, совместимые со спецификацией OData. Веб-служба ожидает запрос и ответ в указанном ниже формате.

• Запрос: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName

• Ответ: {"@odata.context": "https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity", "ProductID": 1, "ProductName": "Chai"}

URL-адрес OData представляет запрос столбцов ProductID и ProductName для продукта с ID 1. Можно использовать FOR JSON для форматирования выходных данных, как ожидалось в SQL Server.

SELECT 'https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity' AS '@odata.context',
       ProductID,
       Name AS ProductName
FROM Production.Product
WHERE ProductID = 1
FOR JSON AUTO;

Выходные данные этого запроса — это текст JSON, который полностью соответствует спецификации OData. Форматирование и экранирование обрабатываются SQL Server. SQL Server может также выдать результаты запроса в любом формате, таком как OData JSON или GeoJSON.

Проверка встроенной поддержки JSON с образцом базы данных AdventureWorks

Чтобы получить пример базы данных AdventureWorks, скачайте по крайней мере файл базы данных и файлы примеров и скриптов из GitHub.

После восстановления образца базы данных в экземпляр SQL Server извлеките файл примеров, а затем откройте JSON Sample Queries procedures views and indexes.sql файл из папки JSON. Выполните сценарии в этом файле, чтобы переформатировать некоторые данные как данные JSON, протестируйте образцы запросов и отчеты по данным JSON, индексируйте данные JSON, а затем импортируйте и экспортируйте JSON.

Вот, что делать с помощью скриптов, включенных в файл.

1. Выполнить денормализацию существующей схемы для создания столбцов данных JSON.

   • Сохранить информацию из SalesReasons, SalesOrderDetails, SalesPerson, Customer и других таблиц, содержащих информацию о заказах на продажу, в столбцах JSON в таблице SalesOrder_json.

   • Храните данные из EmailAddresses таблицы и PersonPhone таблицы в Person_json виде массивов объектов JSON.

2. Создайте процедуры и представления для запроса данных JSON.

3. Проиндексируйте данные JSON. Создайте индексы свойств JSON и полнотекстовые индексы.

4. Импортируйте и экспортируйте JSON. Создать и запустить процедуры для экспорта содержимого таблиц Person и SalesOrder в качестве результатов в формате JSON, а затем импортировать и обновить таблицы Person и SalesOrder, используя входные данные JSON.

5. Выполните примеры запросов. Выполните несколько запросов, вызывающих хранимые процедуры и представления, которые были созданы при выполнении шагов 2 и 4.

6. Очистите скрипты. Не выполняйте это действие, если хотите оставить хранимые процедуры и представления, которые были созданы при выполнении шагов 2 и 4.

Связанный контент

• SELECT — предложение FOR (Transact-SQL)
• OPENJSON (Transact-SQL)
• Функции JSON (Transact-SQL)
• ISJSON (Transact-SQL)
• JSON_VALUE (Transact-SQL)
• JSON_OBJECT (Transact-SQL)
• JSON_ARRAY (Transact-SQL)
• JSON_QUERY (Transact-SQL)
• JSON_MODIFY (Transact-SQL)