Данные JSON в SQL Server

Применимо к: SQL Server 2016 и более поздних версий Azure SQL Database Azure Synapse Analytics Not supported Analytics Platform System (PDW)

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

Функции JSON, которые появились в SQL Server 2016, позволяют объединить принципы 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.

Обзор встроенной поддержки JSON

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

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

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

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

Пример

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

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 с помощью встроенных функций (SQL Server), JSON_VALUE (Transact-SQL)и JSON_QUERY (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;

Результаты

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

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

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

В следующем примере вызывается функция OPENJSON, и массив объектов, хранящийся в переменной @json, преобразуется в набор строк, который можно запросить с помощью стандартной инструкции 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 age dateOfBirth
2 Джон Смит 25
5 Джейн Смит 2005-11-04T12:00:00

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

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

Дополнительные сведения см. в статьях Преобразование данных JSON в строки и столбцы с помощью функции OPENJSON (SQL Server) и 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 age 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 в формате JSON или результаты запросов SQL можно отформатировать как JSON, добавив предложение FOR JSON к инструкции SELECT . FOR JSON позволяет делегировать форматирование выходных данных JSON из клиентских приложений в SQL Server. Дополнительные сведения см. в разделе Форматирование результатов запроса как JSON с помощью предложения FOR JSON (SQL Server).

В следующем примере используется режим 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 (SQL Server) и Предложение FOR (Transact-SQL).

Варианты использования данных 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, и вы хотите использовать для них язык запросов, специально предназначенный для обработки документов JSON, рассмотрите Microsoft Azure Cosmos DB.

Рассмотрим несколько способов применения встроенной поддержки JSON в SQL Server.

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

JSON — это текстовый формат, следовательно, документы JSON могут храниться в столбцах NVARCHAR в Базе данных SQL. Тип NVARCHAR поддерживается во всех подсистемах SQL Server, поэтому вы можете помещать документы JSON в таблицы с индексами CLUSTERED 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, можно импортировать данные в SQL Server с помощью OPENJSON вместо того, чтобы использовать синтаксический анализ данных на уровне приложения.

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 для целей отчетности, JSON можно преобразовать в реляционный формат с помощью OPENJSON. После этого подготовьте отчеты, используя стандартный язык 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 вы можете делегировать форматирование в SQL Server с помощью FOR JSON.

Например, можно сформировать выходные данные 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 и базе данных SQL Azure

Видео Майкрософт

Примечание

Некоторые ссылки на видео в этом разделе могут не работать в данный момент. Корпорация Майкрософт переносит содержимое, которое ранее транслировалось канале Channel 9, на новую платформу. Мы будем обновлять ссылки по мере переноса видео на новую платформу.

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

Создание REST API с SQL Server с помощью функций JSON

Дальнейшие действия