Данные JSON в SQL Server

Статья
08/20/2024

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics

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 требуется уровень совместимости базы данных 130 или более поздней версии.

Вот пример текста JSON:

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

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

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

Схема, показывающая обзор встроенной поддержки JSON.

Основные возможности JSON, предоставляемые SQL Server и базой данных SQL

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

Тип данных JSON

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

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

Примечание.

Тип данных JSON в настоящее время находится в предварительной версии для База данных SQL Azure и Управляемый экземпляр SQL Azure (настроен с помощью политики обновления always-up-up).

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

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

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

ISJSON (Transact-SQL) проверяет наличие в строке допустимых данных JSON.
JSON_VALUE (Transact-SQL) извлекает из строки JSON скалярное значение.
JSON_QUERY (Transact-SQL) извлекает из строки JSON объект или массив.
JSON_MODIFY (Transact-SQL) изменяет значение в строке JSON.

Пример

В следующем примере запрос использует реляционные и 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 (Transact-SQL) и JSON_QUERY (Transact-SQL) (Transact-SQL).

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

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

DECLARE @json NVARCHAR(MAX);

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

SELECT modifiedJson = @json;

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

{"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 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	возраст	dateOfBirth
2	Джон	Иванов	25
5	Джейн	Иванов		2005-11-04T12:00:00

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

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

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

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

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

DECLARE @json 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	возраст	dateOfBirth	skill
2	Джон	Иванов	25
5	Джейн	Иванов		2005-11-04T12:00:00	SQL
5	Джейн	Иванов		2005-11-04T12:00:00	C#
5	Джейн	Иванов		2005-11-04T12:00:00	Azure

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

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

Примечание.

Преобразование данных из 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 и FOR (Transact-SQL).

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

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

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

Примечание.

Агрегированные функции JSON и в настоящее время находятся в предварительной версии для База данных SQL Azure и Управляемый экземпляр SQL Azure (настроены с помощью политики обновления always-up-up).JSON_ARRAYAGG JSON_OBJECTAGG

Варианты использования данных 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 в 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 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.

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

Выполнить денормализацию существующей схемы для создания столбцов данных JSON.
- Сохранить информацию из SalesReasons, SalesOrderDetails, SalesPerson, Customer и других таблиц, содержащих информацию о заказах на продажу, в столбцах JSON в таблице SalesOrder_json.
- Храните данные из EmailAddresses таблицы и PersonPhone таблицы в Person_json виде массивов объектов JSON.
Создайте процедуры и представления для запроса данных JSON.
Проиндексируйте данные JSON. Создайте индексы свойств JSON и полнотекстовые индексы.
Импортируйте и экспортируйте JSON. Создать и запустить процедуры для экспорта содержимого таблиц Person и SalesOrder в качестве результатов в формате JSON, а затем импортировать и обновить таблицы Person и SalesOrder, используя входные данные JSON.
Выполните примеры запросов. Выполните несколько запросов, вызывающих хранимые процедуры и представления, которые были созданы при выполнении шагов 2 и 4.
Очистите скрипты. Не выполняйте это действие, если хотите оставить хранимые процедуры и представления, которые были созданы при выполнении шагов 2 и 4.

Поделиться через

Данные JSON в SQL Server

Основные возможности JSON, предоставляемые SQL Server и базой данных SQL

Тип данных JSON

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обратная связь

Дополнительные ресурсы

Поделиться через

Данные JSON в SQL Server

Основные возможности JSON, предоставляемые SQL Server и базой данных SQL

Тип данных JSON

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обратная связь

Дополнительные ресурсы