Данные JSON в SQL Server

Относится к: SQL Server 2016 (13.x) и более поздним версиямБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure 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
• CREATE JSON INDEX
• 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)