OPENJSON (Transact-SQL)

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure конечную точку аналитики SQL Azure Synapse Analytics в хранилище Microsoft Fabric в Microsoft Fabric

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

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

Примечание.

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

Проверить уровень совместимости можно в представлении sys.databases или в свойствах базы данных. Изменить уровень совместимости базы данных можно с помощью следующей команды:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

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

Синтаксис

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

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

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

openjson

По умолчанию функция с табличным значением OPENJSON возвращает три столбца, содержащих имя ключа, значение и тип для каждой пары {ключ: значение}, обнаруженной в jsonExpression. Кроме того, можно явно указать схему результирующего набора, который возвращает OPENJSON, указав предложение_with.

предложение_with

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

Аргументы

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

Результаты:

key	значение	type
String_value	Джон	1
DoublePrecisionFloatingPoint_value	45	2
DoublePrecisionFloatingPoint_value	2.3456	2
BooleanTrue_value	true	3
BooleanFalse_value	false	3
Null_value	NULL	0
Array_value	["a","r","r","a","y"]	4
Object_value	{"obj":"ect"}	5

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

path

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

В SQL Server 2017 (14.x) и в База данных SQL Azure можно указать переменную в качестве значения пути.

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

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

Результаты

Ключ	Значение
0	en-GB
1	en-UK
2	de-AT
3	es-AR
4	sr-Cyrl

Когда функция 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

Результаты

Месяц	temp	month_id
Янв	10	0
Февр.	12	1
мар	15	2
Апр	17	3
мая	23	4
Июн	27	5

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

предложение_with

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

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

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

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

Примечание.

При использовании параметра AS JSON столбец тип должен иметь значение NVARCHAR(MAX).

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

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

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

Дополнительные сведения о путях см. в статье Выражения пути JSON (SQL Server).

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

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

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

Примечание.

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

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

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  
 )

Результаты

Число	Дата	Клиент	Количество	Порядок
SO43659	2011-05-31T00:00:00	AW29825	1	{"Number":"SO43659","Date":"2011-05-31T00:00:00"}
SO43661	2011-06-01T00:00:00	AW73565	3	{"Number":"SO43661","Date":"2011-06-01T00:00:00"}

Возвращаемое значение

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

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

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

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

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

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

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

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

Примечание.

Столбцы Key, Value и Type возвращаются только при использовании OPENJSON со схемой по умолчанию. Они недоступны с явной схемой.

Замечания

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

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

В некоторых примерах на этой странице явно указывается режим пути: нестрогий или строгий режим. Режим пути является необязательным. Если режим пути не указан явно, по умолчанию используется нестрогий режим. Дополнительные сведения о режиме пути и выражениях пути см. в статье Выражения пути JSON (SQL Server).

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

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

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

Примеры

Пример 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. У двух объектов есть одинаковое свойство name. В примере используется значение ключа, чтобы исключить повторяющиеся строки из результатов.

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 есть такие свойства, как Quality и Manufacturer. В примере создается отчет, который соединяет каждую строку заказа на продажу с соответствующими причинами покупки. Оператор OPENJSON разворачивает массив JSON причин покупки, как если бы причины хранились в отдельной дочерней таблице. Затем оператор CROSS APPLY соединяет каждую строку заказа на продажу со строками, возвращенными функцией с табличным значением OPENJSON.

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

Совет

Если необходимо развернуть массивы JSON, которые хранятся в отдельных полях, и соединить их с родительскими строками, обычно используется оператор CROSS APPLY Transact-SQL. Дополнительные сведения о CROSS APPLY см. в статье Предложение FROM и JOIN, APPLY, PIVOT (Transact-SQL).

Тот же запрос можно переписать с помощью 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

Результаты

title	street	postcode	lon	шир.
Whole Food Markets	17991 Redmond Way	WA 98052	47.666124	-122.10155
Sears	148th Ave NE	WA 98052	47.63024	-122.141246,17

Пример 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

См. также

• Выражения пути JSON (SQL Server)
• Преобразование данных JSON в строки и столбцы с помощью OPENJSON (SQL Server)
• Использование OPENJSON с схемой по умолчанию (SQL Server)
• Использование OPENJSON с явной схемой (SQL Server)