OPENJSON (Transact-SQL)
Область применения:
SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure Конечная точка аналитики Synapse Analytics 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.
При вызове OPENJSON со схемой по умолчанию (то есть без указания явной схемы в предложении WITH) функция возвращает таблицу со следующими столбцами:
Key. Значение nvarchar(4000), содержащее имя указанного свойства или индекс элемента в указанном массиве. Этот столбец имеет параметры сортировки BIN2.
Значение. Значение типа nvarchar(max), содержащее значение свойства. Значение столбца наследует параметры сортировки из jsonExpression.
Тип. Целочисленное значение, содержащее тип значения. Столбец Type возвращается только при использовании OPENJSON со схемой по умолчанию. Столбец Type имеет одно из следующих значений:
Значение столбца Type Тип данных JSON 0 null 1 строка 2 number 3 true/false 4 array 5 объект
Возвращаются только свойства первого уровня. Если текст JSON имеет неправильный формат, выполнение инструкции завершается с ошибкой.
При вызове 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
См. также
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по