Проверка, построение запросов и изменение данных JSON с помощью встроенных функций (SQL Server)Validate, Query, and Change JSON Data with Built-in Functions (SQL Server)

Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL DatabaseПрименимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database

Встроенная поддержка JSON включает следующие встроенные функции, кратко описанные в этой статье.The built-in support for JSON includes the following built-in functions described briefly in this topic.

  • ISJSON проверяет наличие допустимых данных JSON в строке.ISJSON tests whether a string contains valid JSON.

  • JSON_VALUE извлекает скалярное значение из строки JSON.JSON_VALUE extracts a scalar value from a JSON string.

  • JSON_QUERY извлекает объект или массив из строки JSON.JSON_QUERY extracts an object or an array from a JSON string.

  • JSON_MODIFY обновляет значение свойства в строке JSON и возвращает обновленную строку JSON.JSON_MODIFY updates the value of a property in a JSON string and returns the updated JSON string.

Текст JSON для примеров на этой страницеJSON text for the examples on this page

В примерах на этой странице используется текст JSON, аналогичный содержимому в следующем примере:The examples on this page use the JSON text similar to the content shown in the following example:

{
  "id": "WakefieldFamily",
  "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
  ],
  "children": [
      {
        "familyName": "Merriam",
        "givenName": "Jesse",
        "gender": "female",
        "grade": 1,
        "pets": [
            { "givenName": "Goofy" },
            { "givenName": "Shadow" }
        ]
      },
      { 
        "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 8 }
  ],
  "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
  "creationDate": 1431620462,
  "isRegistered": false
}

Этот документ JSON, содержащий вложенные сложные элементы, хранится в следующем образце таблицы:This JSON document, which contains nested complex elements, is stored in the following sample table:

CREATE TABLE Families (
   id int identity constraint PK_JSON_ID primary key,
   doc nvarchar(max)
)

Проверка строки JSON с помощью функции ISJSONValidate JSON text by using the ISJSON function

Функция ISJSON проверяет наличие допустимых данных JSON в строке.The ISJSON function tests whether a string contains valid JSON.

В приведенном ниже примере возвращаются строки, в которых столбец JSON содержит допустимый текст JSON.The following example returns rows in which the JSON column contains valid JSON text. Обратите внимание, что без явного ограничения JSON в столбце NVARCHAR можно ввести любой текст:Note that without explicit JSON constraint, you can enter any text in the NVARCHAR column:

SELECT *
FROM Families
WHERE ISJSON(doc) > 0 

Дополнительные сведения см. в разделе ISJSON (Transact-SQL).For more info, see ISJSON (Transact-SQL).

Для извлечения значения из строки JSON используется функция JSON_VALUEExtract a value from JSON text by using the JSON_VALUE function

Функция JSON_VALUE извлекает скалярное значение из строки JSON.The JSON_VALUE function extracts a scalar value from a JSON string. Следующий запрос вернет документы, в которых поле JSON id соответствует значению AndersenFamily, с упорядочением по полям JSON city и state:The following query will return the documents where the id JSON field matches the value AndersenFamily, ordered by city and state JSON fields:

SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC, JSON_VALUE(f.doc, '$.address.state') ASC

Результаты этого запроса показаны в приведенной ниже таблице.The results of this query are shown in the following table:

ИмяName ГородCity ОкругCounty
AndersenFamilyAndersenFamily Нью-ЙоркNY ManhattanManhattan

Дополнительные сведения см. в разделе JSON_VALUE (Transact-SQL).For more info, see JSON_VALUE (Transact-SQL).

Для извлечения объекта или массива из строки JSON используется функция JSON_QUERYExtract an object or an array from JSON text by using the JSON_QUERY function

Функция JSON_QUERY извлекает объект или массив из строки JSON.The JSON_QUERY function extracts an object or an array from a JSON string. В следующем примере показано, как можно вернуть фрагмент JSON в результатах запроса.The following example shows how to return a JSON fragment in query results.

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
       JSON_QUERY(f.doc, '$.parents') AS Parents,
       JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'

Результаты этого запроса показаны в приведенной ниже таблице.The results of this query are shown in the following table:

АдресAddress ParentsParents Parent0Parent0
{ "state": "NY", "county": "Manhattan", "city": "NY" }{ "state": "NY", "county": "Manhattan", "city": "NY" } [{ "familyName": "Wakefield", "givenName": "Robin" }, {"familyName": "Miller", "givenName": "Ben" } ][{ "familyName": "Wakefield", "givenName": "Robin" }, {"familyName": "Miller", "givenName": "Ben" } ] { "familyName": "Wakefield", "givenName": "Robin" }{ "familyName": "Wakefield", "givenName": "Robin" }

Дополнительные сведения см. в разделе JSON_QUERY (Transact-SQL).For more info, see JSON_QUERY (Transact-SQL).

Анализ вложенных коллекций JSONParse nested JSON collections

Функция OPENJSON позволяет преобразовать вложенный массив JSON в набор строк, а затем присоединить его к родительскому элементу.OPENJSON function enables you to transform JSON sub-array into the rowset and then join it with the parent element. Например, можно вернуть все документы о семьях и присоединить их к объектам children, которые хранятся в виде внутреннего массива JSON:As an example, you can return all family documents, and "join" them with their children objects that are stored as an inner JSON array:

SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       c.givenName, c.grade
FROM Families f
        CROSS APPLY OPENJSON(f.doc, '$.children')
            WITH(grade int, givenName nvarchar(100))  c

Результаты этого запроса показаны в приведенной ниже таблице.The results of this query are shown in the following table:

ИмяName ГородCity givenNamegivenName gradegrade
AndersenFamilyAndersenFamily Нью-ЙоркNY JesseJesse 11
AndersenFamilyAndersenFamily Нью-ЙоркNY LisaLisa 88

В результате мы получаем две строки, так как одна родительская строка объединена с двумя дочерними строками, созданными путем анализа двух элементов дочернего подмассива.We are getting two rows as a result because one parent row is joined with two child rows produced by parsing two elements of the children subarray. Функция OPENJSON анализирует фрагмент children из столбца doc и возвращает grade и givenName из каждого элемента в виде набора строк.OPENJSON function parses children fragment from the doc column and returns grade and givenName from each element as a set of rows. Этот набор строк можно объединить с родительским документом.This rowset can be joined with the parent document.

Запрос вложенных иерархических подмассивов JSONQuery nested hierarchical JSON sub-arrays

Вы можете использовать несколько вызовов CROSS APPLY OPENJSON для запроса вложенных структур JSON.You can apply multiple CROSS APPLY OPENJSON calls in order to query nested JSON structures. В документе JSON, используемом в этом примере, есть вложенный массив с именем children, каждый дочерний элемент которого имеет вложенный массив pets.The JSON document used in this example has a nested array called children, where each child has nested array of pets. Следующий запрос будет анализировать дочерние элементы из каждого документа, возвращать каждый объект массива в виде строки, а затем анализировать массив pets:The following query will parse children from each document, return each array object as row, and then parse pets array:

SELECT  familyName,
    c.givenName AS childGivenName,
    c.firstName AS childFirstName,
    p.givenName AS petName 
FROM Families f 
    CROSS APPLY OPENJSON(f.doc) 
        WITH (familyName nvarchar(100), children nvarchar(max) AS JSON)
        CROSS APPLY OPENJSON(children) 
        WITH (givenName nvarchar(100), firstName nvarchar(100), pets nvarchar(max) AS JSON) as c
            OUTER APPLY OPENJSON (pets)
            WITH (givenName nvarchar(100))  as p

Первый вызов OPENJSON вернет фрагмент массива children с помощью предложения AS JSON.The first OPENJSON call will return fragment of children array using AS JSON clause. Этот фрагмент массива будет передан во вторую функцию OPENJSON, которая вернет givenName, firstName каждого дочернего элемента, а также массив pets.This array fragment will be provided to the second OPENJSON function that will return givenName, firstName of each child, as well as the array of pets. Массив pets будет передан в третью функцию OPENJSON, которая вернет значение givenName для домашнего животного.The array of pets will be provided to the third OPENJSON function that will return the givenName of the pet. Результаты этого запроса показаны в приведенной ниже таблице.The results of this query are shown in the following table:

familyNamefamilyName childGivenNamechildGivenName childFirstNamechildFirstName petNamepetName
AndersenFamilyAndersenFamily JesseJesse MerriamMerriam GoofyGoofy
AndersenFamilyAndersenFamily JesseJesse MerriamMerriam ShadowShadow
AndersenFamilyAndersenFamily LisaLisa MillerMiller NULL

Корневой документ объединяется с двумя строками children, возвращаемыми при первом вызове OPENJSON(children), в результате чего получаются две строки (или два кортежа).The root document is joined with two children rows returned by first OPENJSON(children) call making two rows (or tuples). Затем каждая строка объединяется с новыми строками, созданными функцией OPENJSON(pets), с помощью оператора OUTER APPLY.Then each row is joined with the new rows generated by OPENJSON(pets) using OUTER APPLY operator. У Jesse два домашних животных, поэтому (AndersenFamily, Jesse, Merriam) объединяется с двумя строками, созданными для Goofy и Shadow.Jesse has two pets, so (AndersenFamily, Jesse, Merriam) is joined with two rows generated for Goofy and Shadow. У Lisa нет домашних животных, поэтому функция OPENJSON(pets) не возвращает строк для этого кортежа.Lisa doesn't have the pets, so there are no rows returned by OPENJSON(pets) for this tuple. Однако, так как используется оператор OUTER APPLY, в столбце будет значение NULL.However, since we are using OUTER APPLY we are getting NULL in the column. Если использовать CROSS APPLY вместо OUTER APPLY, Lisa не будет возвращаться в результатах из-за отсутствия строк домашних животных, которые можно было бы объединить с этим кортежем.If we put CROSS APPLY instead of OUTER APPLY, Lisa would not be returned in the result because there are no pets rows that could be joined with this tuple.

Сравнение JSON_VALUE и JSON_QUERYCompare JSON_VALUE and JSON_QUERY

Основное различие между JSON_VALUE и JSON_QUERY заключается в том, что JSON_VALUE возвращает скалярное значение, а JSON_QUERY возвращает объект или массив.The key difference between JSON_VALUE and JSON_QUERY is that JSON_VALUE returns a scalar value, while JSON_QUERY returns an object or an array.

Рассмотрим следующий пример данных в формате JSON.Consider the following sample JSON text.

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}  

В приведенном примере элементы "а" и "c" являются строковыми значениями, а элемент "b" — массивом.In this sample JSON text, data members "a" and "c" are string values, while data member "b" is an array. JSON_VALUE и JSON_QUERY возвращают следующие результаты:JSON_VALUE and JSON_QUERY return the following results:

ПутьPath JSON_VALUE возвращаетJSON_VALUE returns JSON_QUERY возвращаетJSON_QUERY returns
$ NULL или ошибкаNULL or error { "a": "[1,2]", "b": [1,2], "c":"hi"}
$.a$.a [1,2][1,2] NULL или ошибкаNULL or error
$.b$.b NULL или ошибкаNULL or error [1,2][1,2]
$.b [0]$.b[0] 11 NULL или ошибкаNULL or error
$.c$.c hihi NULL или ошибкаNULL or error

Тестирование JSON_VALUE и JSON_QUERY на образце базы данных AdventureWorksTest JSON_VALUE and JSON_QUERY with the AdventureWorks sample database

Чтобы проверить работу встроенных функций, описанных в этой статье, запустите следующие примеры. Они обращаются к базе данных AdventureWorks.Test the built-in functions described in this topic by running the following examples with the AdventureWorks sample database. Сведения о получении AdventureWorks, а также о добавлении данных JSON для тестирования с помощью скрипта см. в разделе Проверка встроенной поддержки JSON.For info about where to get AdventureWorks, and about how to add JSON data for testing by running a script, see Test drive built-in JSON support.

В следующих примерах столбец Info таблицы SalesOrder_json содержит текст в формате JSON.In the following examples, the Info column in the SalesOrder_json table contains JSON text.

Пример 1. Возвращение стандартных столбцов и данных JSONExample 1 - Return both standard columns and JSON data

Следующий запрос возвращает значения и из стандартных реляционных столбцов, и из столбца JSON.The following query returns values from both standard relational columns and from a JSON column.

SELECT SalesOrderNumber, OrderDate, Status, ShipDate, Status, AccountNumber, TotalDue,
 JSON_QUERY(Info,'$.ShippingInfo') ShippingInfo,
 JSON_QUERY(Info,'$.BillingInfo') BillingInfo,
 JSON_VALUE(Info,'$.SalesPerson.Name') SalesPerson,
 JSON_VALUE(Info,'$.ShippingInfo.City') City,
 JSON_VALUE(Info,'$.Customer.Name') Customer,
 JSON_QUERY(OrderItems,'$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0

Пример 2. Агрегирование и фильтрация значений JSONExample 2- Aggregate and filter JSON values

Следующий запрос рассчитывает промежуточные итоги по имени клиента (хранятся в формате JSON) и состояние (хранится в обычном столбце).The following query aggregates subtotals by customer name (stored in JSON) and status (stored in an ordinary column). Затем результаты фильтруются по городу (в формате JSON) и по дате заказа OrderDate (в обычном столбце).Then it filters the results by city (stored in JSON) and OrderDate (stored in an ordinary column).

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid=3;

SET @city=N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer, Status, SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID=@territoryid
 AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
 AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'), Status
HAVING SUM(SubTotal)>1000

Обновление значений свойств в тексте JSON с помощью функции JSON_MODIFYUpdate property values in JSON text by using the JSON_MODIFY function

Функция JSON_MODIFY изменяет значение свойства в строке JSON и возвращает обновленную строку JSON.The JSON_MODIFY function updates the value of a property in a JSON string and returns the updated JSON string.

В следующем примере показано изменение значения свойства JSON в переменной, содержащей JSON-данные.The following example updates the value of a JSON property in a variable that contains JSON.

SET @info = JSON_MODIFY(@jsonInfo, "$.info.address[0].town", 'London')    

Дополнительные сведения см. в разделе JSON_MODIFY (Transact-SQL).For more info, see JSON_MODIFY (Transact-SQL).

Дополнительные сведения о 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 videos:

См. также:See Also

ISJSON (Transact-SQL) ISJSON (Transact-SQL)
JSON_VALUE (Transact-SQL) JSON_VALUE (Transact-SQL)
JSON_QUERY (Transact-SQL) JSON_QUERY (Transact-SQL)
JSON_MODIFY (Transact-SQL) JSON_MODIFY (Transact-SQL)
Выражения пути JSON (SQL Server)JSON Path Expressions (SQL Server)