OPENJSON (Transact-SQL)

Применимо к: SQL Server 2016 (13.x) и более поздним версиям,Azure SQL Database,Azure SQL Managed Instance,Azure Synapse Analytics,конечной точке аналитики SQL в Microsoft Fabric,хранилищу в Microsoft Fabric,SQL базе данных в предварительной версии Microsoft Fabric

Функция OPENJSON с табличным значением анализирует текст JSON и возвращает объекты и свойства из входных данных JSON в виде строк и столбцов. Другими словами, OPENJSON предоставляет представление набора строк по документу JSON. Вы можете явно указать столбцы в наборе строк и пути к свойствам JSON, используемые для заполнения столбцов. Так как OPENJSON возвращает набор строк, можно использовать OPENJSON в FROM предложении инструкции Transact-SQL так же, как можно использовать любую другую функцию таблицы, представления или табличного значения.

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

Соглашения о синтаксисе Transact-SQL

Syntax

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

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

openjson

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

with_clause

With_clause содержит список столбцов со своими типами для OPENJSON возврата. По умолчанию OPENJSON сопоставляет ключи в jsonExpression с именами столбцов в with_clause (в данном случае совпадения означает, что это учитывает регистр). Если имя столбца не соответствует имени ключа, можно указать необязательный column_path, который является выражением пути JSON , ссылающимся на ключ в jsonExpression.

Arguments

jsonExpression

Символьное выражение Юникода, содержащее текст JSON.

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

DECLARE @json NVARCHAR(2048) = N'{
   "String_value": "John",
   "DoublePrecisionFloatingPoint_value": 45,
   "DoublePrecisionFloatingPoint_value": 2.3456,
   "BooleanTrue_value": true,
   "BooleanFalse_value": false,
   "Null_value": null,
   "Array_value": ["a","r","r","a","y"],
   "Object_value": {"obj":"ect"}
}';

SELECT * FROM OpenJson(@json);

Results:

• DoublePrecisionFloatingPoint_value соответствует IEEE-754.

path

Необязательный выражение пути JSON, которое ссылается на объект или массив в jsonExpression. OPENJSON ищет текст JSON в указанной позиции и анализирует только указанный фрагмент. Дополнительные сведения см. в разделе "Выражения пути JSON".

Можно указать переменную в качестве значения пути. (Это не поддерживается в SQL Server 2016 (13.x) и более ранних версиях.)

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

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json,'$.path.to."sub-object"')

Results

При OPENJSON анализе массива JSON функция возвращает индексы элементов в тексте JSON в виде ключей.

Сравнение, используемое для сопоставления шагов пути со свойствами выражения JSON, выполняется с учетом регистра и без учета параметров сортировки (сравнение BIN2).

Идентификатор элемента массива

Функция OPENJSON в бессерверном пуле SQL в Azure Synapse Analytics может автоматически создавать идентификатор каждой строки, возвращаемой в результате. Столбец идентификаторов указывается с помощью выражения $.sql:identity() в пути JSON после определения столбца. В столбце с этим значением в выражении пути JSON будет создаваться уникальный отсчитываемый от 0 номер для каждого элемента в массиве JSON, который анализирует функция. Значение идентификатора представляет позицию или индекс элемента массива.

DECLARE @array VARCHAR(MAX);
SET @array = '[{"month":"Jan", "temp":10},{"month":"Feb", "temp":12},{"month":"Mar", "temp":15},
               {"month":"Apr", "temp":17},{"month":"May", "temp":23},{"month":"Jun", "temp":27}
              ]';

SELECT * FROM OPENJSON(@array)
        WITH (  month VARCHAR(3),
                temp int,
                month_id tinyint '$.sql:identity()') as months

Results

Идентификатор доступен только в бессерверном пуле SQL в Synapse Analytics.

with_clause

Явным образом определяет выходную схему для возвращаемой OPENJSON функции. Необязательный with_clause может содержать следующие элементы:

colName

Имя выходного столбца.

По умолчанию OPENJSON используется имя столбца для сопоставления свойства в тексте JSON. Например, если указать столбец в схеме, name пытается заполнить этот столбец OPENJSON свойством "name" в тексте JSON. Это сопоставление по умолчанию можно переопределить с помощью аргумента column_path .

type

Тип данных для выходного столбца.

column_path

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

Используйте column_path для переопределения правил сопоставления по умолчанию, если имя выходного столбца не соответствует имени свойства.

Сравнение, используемое для сопоставления шагов пути со свойствами выражения JSON, выполняется с учетом регистра и без учета параметров сортировки (сравнение BIN2).

Дополнительные сведения о путях см. в разделе "Выражения пути JSON".

AS JSON

AS JSON Используйте параметр в определении столбца, чтобы указать, что указанное свойство содержит внутренний объект JSON или массив. Если указать AS JSON этот параметр, тип столбца должен быть nvarchar(MAX).

• Если для столбца не указано AS JSON , функция возвращает скалярное значение (например, int, string, true, false) из указанного свойства JSON по указанному пути. Если путь представляет объект или массив, а свойство не удается найти по указанному пути, функция возвращается NULL в lax режиме или возвращает ошибку в режиме.strict Это поведение аналогично поведению JSON_VALUE функции.

• При указании AS JSON столбца функция возвращает фрагмент JSON из указанного свойства JSON по указанному пути. Если путь представляет скалярное значение, а свойство не удается найти по указанному пути, функция возвращается NULL в lax режиме или возвращает ошибку в режиме.strict Это поведение аналогично поведению JSON_QUERY функции.

Например, следующий запрос возвращает и форматирует элементы массива:

DECLARE @json NVARCHAR(MAX) = 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  
    }  
  }
]'  

SELECT *
FROM OPENJSON ( @json )  
WITH (   
              Number   VARCHAR(200)   '$.Order.Number',  
              Date     DATETIME       '$.Order.Date',  
              Customer VARCHAR(200)   '$.AccountNumber',  
              Quantity INT            '$.Item.Quantity',  
              [Order]  NVARCHAR(MAX)  AS JSON  
 )

Results

Return value

Столбцы, возвращаемые OPENJSON функцией, зависят от WITH параметра.

• При вызове OPENJSON схемы по умолчанию, то есть если в предложении не указана явная схема WITH , функция возвращает таблицу со следующими столбцами:

  • Key. Значение nvarchar(4000), содержащее имя указанного свойства или индекс элемента в указанном массиве. Столбец key имеет параметры сортировки BIN2.

  • Value. Значение nvarchar(MAX), содержащее значение свойства. Столбец value наследует параметры сортировки из jsonExpression.

  • Type. Целое значение, содержащее тип значения. Столбец Type возвращается только при использовании OPENJSON со схемой по умолчанию. Столбец type имеет одно из следующих значений:

    Значение столбца Type	Тип данных JSON
    0	null
    1	string
    2	number
    3	true/false
    4	array
    5	object

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

• При вызове OPENJSON и указании явной схемы в WITH предложении функция возвращает таблицу со схемой, определенной в предложении WITH .

Remarks

json_path, используемый во втором аргументе или в with_clause, может начинаться с ключевого OPENJSON слова или strict ключевого lax слова.

• В lax режиме не вызывает ошибку, OPENJSON если объект или значение указанного пути не удается найти. Если путь не найден, OPENJSON возвращает пустой результирующий набор или NULL значение.
• В strictрежиме OPENJSON возвращается ошибка, если путь не найден.

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

Имена столбцов в with_clause совпадают с ключами в тексте JSON. Если указать имя столбца [Address.Country], он сопоставляется с ключом Address.Country. Если требуется сослаться на вложенный ключ Country в объекте Address, нужно указать путь $.Address.Country в пути столбца.

json_path может содержать ключи с буквенно-цифровыми символами. Экранируйте имя ключа в json_path с двойными кавычками, если в ключах есть специальные символы. Например, $."my key $1".regularKey."key with . dot" соответствует значению 1 в следующем тексте JSON:

{
  "my key $1": {
    "regularKey":{
      "key with . dot": 1
    }
  }
}

Examples

Пример 1. Преобразование массива JSON во временную таблицу

В следующем примере список идентификаторов представлен как массив чисел JSON. Запрос преобразует массив JSON в таблицу идентификаторов и выбирает все продукты с указанными идентификаторами.

DECLARE @pSearchOptions NVARCHAR(4000) = N'[1,2,3,4]'

SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
 ON product.productTypeID = productTypes.value

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

SELECT *
FROM products
WHERE product.productTypeID IN (1,2,3,4)

Пример 2. Слияние свойств из двух объектов JSON

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

DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX)

SET @json1=N'{"name": "John", "surname":"Doe"}'

SET @json2=N'{"name": "John", "age":45}'

SELECT *
FROM OPENJSON(@json1)
UNION ALL
SELECT *
FROM OPENJSON(@json2)
WHERE [key] NOT IN (SELECT [key] FROM OPENJSON(@json1))

Пример 3. Соединение строк с данными JSON, хранящимися в ячейках таблицы, с помощью CROSS APPLY

В следующем примере в таблице SalesOrderHeader есть текстовый столбец SalesReason, содержащий массив SalesOrderReasons в формате JSON. Объекты SalesOrderReasons содержат такие свойства, как качество и производитель. В примере создается отчет, который соединяет каждую строку заказа на продажу с соответствующими причинами покупки. Оператор OPENJSON расширяет массив причин продаж JSON, как если бы причины хранились в отдельной дочерней таблице. CROSS APPLY Затем оператор присоединяет каждую строку заказа продаж к строкам, возвращаемым OPENJSON табличной функцией.

SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)

Тот же запрос можно переписать с помощью OPENJSON с явно определенной схемой возвращаемых строк:

SELECT SalesOrderID, OrderDate, value AS Reason  
FROM Sales.SalesOrderHeader  
     CROSS APPLY OPENJSON (SalesReasons) WITH (value NVARCHAR(100) '$')

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

Пример 4. Объединение реляционных строк и элементов JSON с помощью CROSS APPLY

Следующий запрос объединяет реляционные строки и элементы JSON в результаты, показанные в следующей таблице.

SELECT store.title, location.street, location.lat, location.long  
FROM store  
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')   
     WITH (street VARCHAR(500) ,  postcode VARCHAR(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     AS location

Results

Пример 5. Импорт данных JSON в SQL Server

В следующем примере загружается весь объект JSON в таблицу SQL Server.

DECLARE @json NVARCHAR(max)  = N'{  
  "id" : 2,  
  "firstName": "John",  
  "lastName": "Smith",  
  "isAlive": true,  
  "age": 25,  
  "dateOfBirth": "2015-03-25T12:00:00",  
  "spouse": null  
  }';  

  INSERT INTO Person  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id INT,  
        firstName NVARCHAR(50), lastName NVARCHAR(50),   
        isAlive BIT, age INT,  
        dateOfBirth DATETIME, spouse NVARCHAR(50))

Пример 6. Простой пример с содержимым JSON

--simple cross apply example
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

Related content

• Выражения пути JSON
• Анализ и преобразование данных JSON с помощью OPENJSON
• Использование OPENJSON с схемой по умолчанию
• Использование OPENJSON с явной схемой