Данные JSON в SQL ServerJSON data in SQL Server

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure даAzure Synapse Analytics (хранилище данных SQL) нетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

JSON — это популярный формат текстовых данных, который используется для обмена данными в современных веб- и мобильных приложениях.JSON is a popular textual data format that's used for exchanging data in modern web and mobile applications. Кроме того, JSON используется для хранения неструктурированных данных в файлах журналов или базах данных NoSQL, таких как Microsoft Azure Cosmos DB.JSON is also used for storing unstructured data in log files or NoSQL databases such as Microsoft Azure Cosmos DB. Многие веб-службы REST возвращают результаты в формате текста JSON или принимают данные в формате JSON.Many REST web services return results that are formatted as JSON text or accept data that's formatted as JSON. Например, большинство служб Azure, таких как поиск Azure, служба хранилища Azure и Azure Cosmos DB, имеют конечные точки REST, которые возвращают или принимают JSON.For example, most Azure services, such as Azure Search, Azure Storage, and Azure Cosmos DB, have REST endpoints that return or consume JSON. JSON — это также основной формат обмена данными между веб-страницами и веб-серверами с помощью вызовов AJAX.JSON is also the main format for exchanging data between webpages and web servers by using AJAX calls.

Функции JSON в SQL Server позволяют объединить принципы NoSQL и реляционных баз данных в одной базе данных.JSON functions in SQL Server enable you to combine NoSQL and relational concepts in the same database. Теперь вы можете объединять в одной таблице классические реляционные столбцы со столбцами, которые содержат документы в формате текста JSON, анализировать и импортировать документы JSON в реляционные структуры или форматировать реляционные данные в текст JSON.Now you can combine classic relational columns with columns that contain documents formatted as JSON text in the same table, parse and import JSON documents in relational structures, or format relational data to JSON text. Посмотрите видео, чтобы понять, как функции JSON объединяют реляционные принципы и NoSQL в SQL Server и базе данных SQL Azure:You see how JSON functions connect relational and NoSQL concepts in SQL Server and Azure SQL Database in the following video:

JSON as a bridge between NoSQL and relational worlds (JSON как мост между NoSQL и реляционными решениями)JSON as a bridge between NoSQL and relational worlds

Вот пример текста JSON:Here's an example of JSON text:

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

С помощью встроенных функций и операторов SQL Server вы можете выполнять указанные ниже действия с текстом JSON.By using SQL Server built-in functions and operators, you can do the following things with JSON text:

  • Синтаксический анализ текста JSON, а также считывание и изменение значений.Parse JSON text and read or modify values.
  • Преобразования массивов объектов JSON в табличный формат.Transform arrays of JSON objects into table format.
  • Выполнение любого запроса Transact SQL к преобразованным объектам JSON.Run any Transact-SQL query on the converted JSON objects.
  • Форматирование результатов запросов Transact-SQL в формате JSON.Format the results of Transact-SQL queries in JSON format.

Общие сведения о встроенной поддержке JSONOverview of built-in JSON support

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

В следующих разделах описываются основные возможности, предоставляемые SQL Server со встроенной поддержкой JSON.The next sections discuss the key capabilities that SQL Server provides with its built-in JSON support. Посмотрите видео, чтобы понять, как использовать функции и операторы JSON:You can see how to use JSON functions and operators in the following video:

SQL Server 2016 and JSON Support (SQL Server 2016 и поддержка JSON)SQL Server 2016 and JSON Support

Извлечение значений из текста JSON и их использование в запросахExtract values from JSON text and use them in queries

Если у вас есть текст JSON, который хранится в таблицах базы данных, вы можете прочитать или изменить значения в тексте JSON с помощью следующих встроенных функций.If you have JSON text that's stored in database tables, you can read or modify values in the JSON text by using the following built-in functions:

ПримерExample

В следующем примере представлен запрос, в котором используются реляционные данные и данные JSON (хранятся в столбце jsonCol) из таблицы:In the following example, the query uses both relational and JSON data (stored in a column named jsonCol) from a table:

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.Applications and tools see no difference between the values taken from scalar table columns and the values taken from JSON columns. Значения из текста JSON можно использовать в любой части запроса Transact-SQL (включая предложения WHERE, ORDER BY или GROUP BY, агрегатные операции с окнами и т. д.).You can use values from JSON text in any part of a Transact-SQL query (including WHERE, ORDER BY, or GROUP BY clauses, window aggregates, and so on). Для ссылок на значения в тексте JSON функции JSON используют синтаксис типа JavaScript.JSON functions use JavaScript-like syntax for referencing values inside JSON text.

Дополнительные сведения см. в статьях Проверка, создание запросов и изменение данных JSON с помощью встроенных функций (SQL Server), JSON_VALUE (Transact-SQL)и JSON_QUERY (Transact-SQL).For more information, see Validate, query, and change JSON data with built-in functions (SQL Server), JSON_VALUE (Transact-SQL), and JSON_QUERY (Transact-SQL).

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

Если вам нужно изменить части текста JSON, используйте функцию JSON_MODIFY (Transact-SQL), чтобы обновить значение свойства в строке JSON и получить обновленную строку JSON.If you must modify parts of JSON text, you can use the JSON_MODIFY (Transact-SQL) function to update the value of a property in a JSON string and return the updated JSON string. В следующем примере показано, как изменить значение свойства в переменной, которая содержит данные в формате JSON.The following example updates the value of a property in a variable that contains 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;

РезультатыResults

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

Преобразование коллекций JSON в набор строкConvert JSON collections to a rowset

Для выполнения запросов JSON в SQL Server никакой особый язык запросов не требуется.You don't need a custom query language to query JSON in SQL Server. Для запроса данных JSON можно использовать стандартные инструкции T-SQL.To query JSON data, you can use standard T-SQL. Если вам нужно создать запрос или отчет по данным JSON, вы можете легко преобразовать данные JSON в строки и столбцы, вызвав функцию набора строк OPENJSON.If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. Дополнительные сведения см. в статье Преобразование данных JSON в строки и столбцы с помощью функции OPENJSON (SQL Server).For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).

В следующем примере вызывается функция OPENJSON, и массив объектов, хранящийся в переменной @json, преобразуется в набор строк, который можно запросить с помощью стандартной инструкции SQL SELECT:The following example calls OPENJSON and transforms the array of objects that is stored in the @json variable to a rowset that can be queried with a standard SQL SELECT statement:

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'
  );

РезультатыResults

IDID firstNamefirstName lastNamelastName ageage dateOfBirthdateOfBirth
22 ДжонJohn СмитSmith 2525
55 ДжейнJane СмитSmith 2005-11-04T12:00:002005-11-04T12:00:00

OPENJSON преобразует массив объектов JSON в таблицу, где каждый объект представлен в отдельной строке, а пары "ключ —значение" возвращаются как ячейки.OPENJSON transforms the array of JSON objects into a table in which each object is represented as one row, and key/value pairs are returned as cells. К выходным данным применяются следующие правила.The output observes the following rules:

  • OPENJSON преобразует значения JSON в типы, указанные в предложении WITH.OPENJSON converts JSON values to the types that are specified in the WITH clause.
  • OPENJSON может обрабатывать плоские пары ключ:значение, а также вложенные объекты с иерархической организацией.OPENJSON can handle both flat key/value pairs and nested, hierarchically organized objects.
  • Все поля в тексте JSON возвращать необязательно.You don't have to return all the fields that are contained in the JSON text.
  • Если значений JSON нет, OPENJSON возвращает значения NULL.If JSON values don't exist, OPENJSON returns NULL values.
  • Путь, обозначенный после указания типа, можно использовать для ссылки на вложенное свойство или просто для ссылки на свойство с другим именем.You can optionally specify a path after the type specification to reference a nested property or to reference a property by a different name.
  • Необязательный префикс strict в пути означает, что значения указанных свойств должны присутствовать в тексте JSON.The optional strict prefix in the path specifies that values for the specified properties must exist in the JSON text.

Дополнительные сведения см. в статьях Преобразование данных JSON в строки и столбцы с помощью функции OPENJSON (SQL Server) и OPENJSON (Transact-SQL).For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server) and OPENJSON (Transact-SQL).

Документы JSON могут содержать вложенные элементы и иерархические данные, которые невозможно напрямую сопоставить со стандартными реляционными столбцами.JSON documents may have sub-elements and hierarchical data that cannot be directly mapped into the standard relational columns. В этом случае можно выполнить сведение иерархии JSON посредством соединения родительской сущности с вложенными массивами.In this case, you can flatten JSON hierarchy by joining parent entity with sub-arrays.

В следующем примере второй объект в массиве содержит вложенный массив, представляющий навыки сотрудника.In the following example, the second object in the array has sub-array representing person skills. Каждый вложенный объект может быть проанализирован с помощью дополнительных вызовов функции OPENJSON:Every sub-object can be parsed using additional OPENJSON function call:

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 *  
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.skills array is returned in the first OPENJSON as original JSON text fragment and passed to another OPENJSON function using APPLY operator. Вторая функция OPENJSON анализирует массив JSON и возвращает строковые значения в виде единого набора строк столбцов, который будет соединен с результатами первой функции OPENJSON.The second OPENJSON function will parse JSON array and return string values as single column rowset that will be joined with the result of the first OPENJSON. Результат этого запроса показан в следующей таблице:The result of this query is shown in the following table:

РезультатыResults

IDID firstNamefirstName lastNamelastName ageage dateOfBirthdateOfBirth skillskill
22 ДжонJohn СмитSmith 2525
55 ДжейнJane СмитSmith 2005-11-04T12:00:002005-11-04T12:00:00 SQLSQL
55 ДжейнJane СмитSmith 2005-11-04T12:00:002005-11-04T12:00:00 C#C#
55 ДжейнJane СмитSmith 2005-11-04T12:00:002005-11-04T12:00:00 AzureAzure

Функция OUTER APPLY OPENJSON соединит сущности первого уровня с вложенным массивом и вернет преобразованный в плоскую структуру набор результатов.OUTER APPLY OPENJSON will join first level entity with sub-array and return flatten resultset. Из-за выполнения операции JOIN вторая строка будет повторяться для каждого навыка.Due to JOIN, the second row will be repeated for every skill.

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

Примечание

Преобразование данных из Хранилища данных SQL Azure в формат JSON или экспорт в формате JSON не поддерживается.Converting Azure SQL Data Warehouse data to JSON or exporting JSON is not supported.

Данные из SQL Server в формате JSON или результаты запросов SQL можно отформатировать как JSON, добавив предложение FOR JSON к инструкции SELECT .Format SQL Server data or the results of SQL queries as JSON by adding the FOR JSON clause to a SELECT statement. FOR JSON позволяет делегировать форматирование выходных данных JSON из клиентских приложений в SQL Server.Use FOR JSON to delegate the formatting of JSON output from your client applications to SQL Server. Дополнительные сведения см. в разделе Форматирование результатов запроса как JSON с помощью предложения FOR JSON (SQL Server).For more information, see Format Query Results as JSON with FOR JSON (SQL Server).

В следующем примере используется режим PATH с предложением FOR JSON.The following example uses PATH mode with the FOR JSON clause:

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

Это FOR JSON отформатирует результаты SQL как текст JSON, который можно предоставить любому приложению, которое понимает JSON.The FOR JSON clause formats SQL results as JSON text that can be provided to any app that understands JSON. Параметр PATH содержит псевдонимы, разделенные точками, в предложении SELECT для вложения объектов в результаты запросов.The PATH option uses dot-separated aliases in the SELECT clause to nest objects in the query results.

РезультатыResults

[
  {
    "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).For more information, see Format query results as JSON with FOR JSON (SQL Server) and FOR Clause (Transact-SQL).

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

Поддержка JSON в SQL Server и базе данных SQL Azure позволяет объединить принципы NoSQL и реляционных баз данных.JSON support in SQL Server and Azure SQL Database lets you combine relational and NoSQL concepts. Вы можете легко преобразовывать реляционные данные в частично структурированные и наоборот.You can easily transform relational to semi-structured data and vice-versa. Однако JSON не заменяет существующие реляционные модели.JSON is not a replacement for existing relational models, however. Ниже приведены некоторые конкретные варианты использования с преимуществами поддержки JSON в SQL Server и базе данных SQL.Here are some specific use cases that benefit from the JSON support in SQL Server and in SQL Database.

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

Рассмотрите возможность денормализации модели данных с полями JSON вместо нескольких дочерних таблиц.Consider denormalizing your data model with JSON fields in place of multiple child tables.

Хранение данных розничной торговли и электронной коммерцииStore retail and e-commerce data

Храните сведения о продуктах, используя в денормализованной модели множество атрибутов переменных для обеспечения гибкости.Store info about products with a wide range of variable attributes in a denormalized model for flexibility.

Обработка данных журнала и данных телеметрииProcess log and telemetry data

Загружайте, запрашивайте и анализируйте данные журнала, хранящиеся в виде JSON-файлов, используя все возможности языка Transact-SQL.Load, query, and analyze log data stored as JSON files with all the power of the Transact-SQL language.

Сохранение частично структурированных данных Интернета вещейStore semi-structured IoT data

Чтобы проанализировать данные Интернета вещей в режиме реального времени, загружайте входящие данные непосредственно в базу данных, а не размещайте их в месте хранения.When you need real-time analysis of IoT data, load the incoming data directly into the database instead of staging it in a storage location.

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

Легко преобразовывайте реляционные данных из базы данных в формат JSON, используемый интерфейсами REST API, которые поддерживают ваш веб-сайт.Transform relational data from your database easily into the JSON format used by the REST APIs that support your web site.

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

SQL Server предоставляет гибридную модель для хранения и обработки реляционных данных и данных JSON с использованием стандартного языка Transact-SQL.SQL Server provides a hybrid model for storing and processing both relational and JSON data by using standard Transact-SQL language. Вы можете формировать коллекции документов JSON в таблицах, устанавливать отношения между ними, комбинировать строго типизированные скалярные столбцы, которые хранятся в таблицах с гибкими парами "ключ —значение", хранящимися в столбцах JSON, и запрашивать скалярные значения и значения JSON в одной таблице или нескольких с использованием полного Transact-SQL.You can organize collections of your JSON documents in tables, establish relationships between them, combine strongly typed scalar columns stored in tables with flexible key/value pairs stored in JSON columns, and query both scalar and JSON values in one or more tables by using full Transact-SQL.

Текст JSON обычно хранится в столбцах VARCHAR или NVARCHAR и индексируется как обычный текст.JSON text is stored in VARCHAR or NVARCHAR columns and is indexed as plain text. Любая функция или компонент SQL Server, которые поддерживают текст, поддерживают и JSON, поэтому в обмене данных между JSON и другими компонентами SQL Server нет практически никаких ограничений.Any SQL Server feature or component that supports text supports JSON, so there are almost no constraints on interaction between JSON and other SQL Server features. JSON можно хранить во временных таблицах или в таблицах в памяти, применять к тексту JSON предикаты безопасности на уровне строк и т. д.You can store JSON in In-memory or Temporal tables, apply Row-Level Security predicates on JSON text, and so on.

Если у вас есть рабочие нагрузки, в которых присутствует только JSON, и вы хотите использовать для них язык запросов, специально предназначенный для обработки документов JSON, рассмотрите Microsoft Azure Cosmos DB.If you have pure JSON workloads where you want to use a query language that's customized for the processing of JSON documents, consider Microsoft Azure Cosmos DB.

Рассмотрим несколько способов применения встроенной поддержки JSON в SQL ServerSQL Server.Here are some use cases that show how you can use the built-in JSON support in SQL ServerSQL Server.

Хранение и индексирование данных JSON в SQL ServerStore and index JSON data in SQL Server

JSON — это текстовый формат, следовательно, документы JSON могут храниться в столбцах NVARCHAR в Базе данных SQL.JSON is a textual format so the JSON documents can be stored in NVARCHAR columns in a SQL Database. Тип NVARCHAR поддерживается во всех подсистемах SQL Server, благодаря чему вы можете помещать документы JSON в таблицы с индексами CLUSTERED COLUMNSTORE, оптимизированные для памяти таблицы, а также во внешние файлы, которые могут считываться с помощью OPENROWSET или PolyBase.Since NVARCHAR type is supported in all SQL Server sub-systems you can put JSON documents in tables with CLUSTERED COLUMNSTORE indexes, memory optimized tables, or external files that can be read using OPENROWSET or PolyBase.

Дополнительные сведения о возможностях хранения, индексирования и оптимизации данных JSON в SQL Server, см. в следующих статьях.To learn more about your options for storing, indexing, and optimizing JSON data in SQL Server, see the following articles:

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

Сведения, которые хранятся в файлах, можно отформатировать как стандартный JSON или JSON с разбивкой на строки.You can format information that's stored in files as standard JSON or line-delimited JSON. SQL Server может импортировать содержимое файлов JSON, проанализировать его с помощью функций OPENJSON или JSON_VALUE и загрузить их в таблицы.SQL Server can import the contents of JSON files, parse it by using the OPENJSON or JSON_VALUE functions, and load it into tables.

  • Если документы JSON хранятся в локальных файлах, на общих сетевых дисках или в хранилище файлов Azure, доступном для SQL Server, данные JSON можно загрузить в SQL Server с помощью массового импорта.If your JSON documents are stored in local files, on shared network drives, or in Azure Files locations that can be accessed by SQL Server, you can use bulk import to load your JSON data into SQL Server.

  • Если файлы JSON с разбивкой на строки хранятся в хранилище BLOB-объектов Azure или в файловой системе Hadoop, вы можете загрузить текст JSON с помощью Polybase, проанализировать его в коде Transact-SQL и загрузить в таблицы.If your line-delimited JSON files are stored in Azure Blob storage or the Hadoop file system, you can use PolyBase to load JSON text, parse it in Transact-SQL code, and load it into tables.

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

Если требуется загрузка данных JSON из внешней службы в SQL Server, можно импортировать данные в SQL Server с помощью OPENJSON вместо того, чтобы использовать синтаксический анализ данных на уровне приложения.If you must load JSON data from an external service into SQL Server, you can use OPENJSON to import the data into SQL Server instead of parsing the data in the application layer.

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 на стороне клиента или загрузить из внешних файлов.You can provide the content of the JSON variable by an external REST service, send it as a parameter from a client-side JavaScript framework, or load it from external files. Результаты можно легко вставить, обновить или объединить из текста JSON в таблицу SQL Server.You can easily insert, update, or merge results from JSON text into a SQL Server table.

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

Если вам нужно отфильтровать или вычислить данные JSON для целей отчетности, JSON можно преобразовать в реляционный формат с помощью OPENJSON.If you must filter or aggregate JSON data for reporting purposes, you can use OPENJSON to transform JSON to relational format. После подготовьте отчеты, используя стандартный Transact-SQLTransact-SQL и встроенные функции.You can then use standard Transact-SQLTransact-SQL and built-in functions to prepare the reports.

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.You can use both standard table columns and values from JSON text in the same query. Для повышения эффективности запроса можно добавить индексы в выражение JSON_VALUE(Tab.json, '$.Status').You can add indexes on the JSON_VALUE(Tab.json, '$.Status') expression to improve the performance of the query. Дополнительные сведения см. в разделе Индексирование данных JSON.For more information, see Index JSON data.

Возврат данных из таблицы SQL Server в формате JSONReturn data from a SQL Server table formatted as JSON

Если у вас есть веб-служба, которая получает данные с уровня базы данных и возвращает их в формате JSON, либо платформы или библиотеки JavaScript, которые принимают данные в формате JSON, вы можете отформатировать выходные данные JSON прямо в запросе SQL.If you have a web service that takes data from the database layer and returns it in JSON format, or if you have JavaScript frameworks or libraries that accept data formatted as JSON, you can format JSON output directly in a SQL query. Вместо написания кода или включения библиотеки для преобразования результатов табличных запросов и последующей сериализации объектов в формате JSON вы можете делегировать форматирование в SQL Server с помощью FOR JSON.Instead of writing code or including a library to convert tabular query results and then serialize objects to JSON format, you can use FOR JSON to delegate the JSON formatting to SQL Server.

Например, можно сформировать выходные данные JSON, совместимые со спецификацией OData.For example, you might want to generate JSON output that's compliant with the OData specification. Веб-служба ожидает запрос и ответ в указанном ниже формате.The web service expects a request and response in the following format:

  • Запрос: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductNameRequest: /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"}Response: {"@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.This OData URL represents a request for the ProductID and ProductName columns for the product with ID 1. FOR JSON можно использовать для форматирования выходных данных для SQL Server.You can use FOR JSON to format the output as expected in 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.The output of this query is JSON text that's fully compliant with the OData spec. Formatting and escaping are handled by SQL Server. SQL Server может также выдать результаты запроса в любом формате, таком как OData JSON или GeoJSON.SQL Server can also format query results in any format, such as OData JSON or GeoJSON.

Проверка встроенной поддержки JSON с образцом базы данных AdventureWorksTest drive built-in JSON support with the AdventureWorks sample database

Чтобы получить образец базы данных AdventureWorks, скачайте по меньшей мере файл базы данных и примеры сценариев в Центре загрузки Майкрософт.To get the AdventureWorks sample database, download at least the database file and the samples and scripts file from Microsoft Download Center.

После восстановления образца базы данных в экземпляре SQL Server 2016 распакуйте файлы образца и откройте файл JSON Sample Queries procedures views and indexes.sql в папке JSON.After you restore the sample database to an instance of SQL Server 2016, extract the samples file, and then open the JSON Sample Queries procedures views and indexes.sql file from the JSON folder. Выполните сценарии в этом файле, чтобы переформатировать некоторые данные как данные JSON, протестируйте образцы запросов и отчеты по данным JSON, индексируйте данные JSON, а затем импортируйте и экспортируйте JSON.Run the scripts in this file to reformat some existing data as JSON data, test sample queries and reports over the JSON data, index the JSON data, and import and export JSON.

Вот, что делать с помощью скриптов, включенных в файл.Here's what you can do with the scripts that are included in the file:

  • Выполнить денормализацию существующей схемы для создания столбцов данных JSON.Denormalize the existing schema to create columns of JSON data.

    • Сохраните данные из SalesReasons, SalesOrderDetails, SalesPerson, Customer и других таблиц, которые содержат сведения, относящиеся к заказам на продажу, в столбцы JSON таблицы SalesOrder_json.Store information from SalesReasons, SalesOrderDetails, SalesPerson, Customer, and other tables that contain information related to sales order into JSON columns in the SalesOrder_json table.

    • Сохраните данные из таблиц EmailAddresses/PersonPhone в таблицу Person_json как массивы объектов JSON.Store information from EmailAddresses/PersonPhone tables in the Person_json table as arrays of JSON objects.

  • Создайте процедуры и представления для запроса данных JSON.Create procedures and views that query JSON data.

  • Проиндексируйте данные JSON.Index JSON data. Создайте индексы свойств JSON и полнотекстовые индексы.Create indexes on JSON properties and full-text indexes.

  • Импортируйте и экспортируйте JSON.Import and export JSON. Создайте и выполните процедуры для экспорта содержимого таблиц Person и SalesOrder как результатов JSON, а затем импортируйте и обновите таблицы Person и SalesOrder, используя входные данные JSON.Create and run procedures that export the content of the Person and the SalesOrder tables as JSON results, and import and update the Person and the SalesOrder tables by using JSON input.

  • Выполните примеры запросов.Run query examples. Выполните несколько запросов, вызывающих хранимые процедуры и представления, которые были созданы при выполнении шагов 2 и 4.Run some queries that call the stored procedures and views that you created in steps 2 and 4.

  • Очистите скрипты.Clean up scripts. Не выполняйте это действие, если хотите оставить хранимые процедуры и представления, которые были созданы при выполнении шагов 2 и 4.Don't run this part if you want to keep the stored procedures and views that you created in steps 2 and 4.

Дополнительные сведения о JSON в SQL Server и базе данных SQL AzureLearn more about JSON in SQL Server and Azure SQL Database

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

Наглядные инструкции по встроенной поддержке JSON в SQL Server и базе данных SQL Azure см. в следующем видео:For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following video:

Using JSON in SQL Server 2016 and Azure SQL Database (Использование JSON в SQL Server 2016 и базе данных SQL Azure)Using JSON in SQL Server 2016 and Azure SQL Database

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

Справочные статьиReference articles