Данные JSON в SQL Server

Область применения: 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, предоставляемые SQL Server и базой данных SQL

В следующих разделах описываются основные возможности, предоставляемые SQL Server со встроенной поддержкой 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'
);

Результирующий набор:

Идентификатор	firstName	lastName	возраст	dateOfBirth
2	Джон	Smith	25
5	Джейн	Smith		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.

Результирующий набор:

Идентификатор	firstName	lastName	возраст	dateOfBirth	skill
2	Джон	Smith	25
5	Джейн	Smith		2005-11-04T12:00:00	SQL
5	Джейн	Smith		2005-11-04T12:00:00	C#
5	Джейн	Smith		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 в 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 или базе данных SQL
• Индексирование данных 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 вместо анализа данных на уровне приложения.

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 и базе данных SQL Azure

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

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

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

• 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)