Введение в использование и применение OPENJSON в SQL
JSON везде!
Понимание того, как работать с JSON, является важным навыком. JSON, сокращение от JavaScript Object Notation , - один из самых популярных способов обмена данными. форматы. Из-за преобладания JavaScript как наиболее широко используемого языка сегодня вопросы, связанные с JSON, в Stack Overflow обошли XML и другие популярные форматы. Пока JavaScript продолжает широко использоваться, эта тенденция, вероятно, сохранится.
В качестве примера из реальной жизни один из программных инструментов, которые разрабатывает моя команда, сохраняет JSON в таблице на SQL Server. Некоторые пользователи спрашивали об эффективных способах изучения JSON. В этой статье рассматриваются некоторые основы работы с JSON в Microsoft SQL Server.
Дополнительные примеры см. В официальной документации Microsoft:
Обратите внимание, что в этих примерах используются функции, ПРИМЕНЯЕМЫЕ К:
- SQL Server 2016 и более поздние версии
- База данных SQL Azure
- Azure Synapse Analytics
Понимание JSON
Формат JSON поддерживает иерархические структуры данных с использованием объектов и массивов. Объект - это пара ключ / значение. Например:
{«Ключ1»: «значение1», «ключ2»: «значение2}
Массив - это список значений. Например:
[«Значение1», «значение2», «значение3»]
В JSON объекты и массивы могут содержать другие объекты или массивы, что придает данным древовидную структуру. Например:
{«Ключ1»: [{«значения1»: [«1», «2»], «значения2»: [«3», «4»]}]}
Обратите внимание, как Key1 связан с массивом, содержащим объекты. У каждого свой массив. Они называются вложенными иерархическими вложенными массивами JSON.
Форматирование JSON упрощает понимание иерархии объектов. Мне нравится использовать бесплатный онлайн-инструмент для форматирования:
{
"key1": [
{
"values1": [
"1",
"2"
],
"values2": [
"3",
"4"
]
}
]
}
Использование OPENJSON для преобразования JSON в SQL Server
Чтобы начать изучение JSON в SQL Server, я рекомендую использовать функцию OPENJSON. Если вам нужен JSON для игры, следующий скрипт создаст таблицу superHero. Он имеет первичный ключ и столбец описания. После создания таблицы в таблицу superHero будет вставлена строка JSON.
CREATE TABLE superHero(
[heroID] [int] PRIMARY KEY CLUSTERED IDENTITY(1,1) NOT NULL,
[description] nvarchar(MAX) NOT NULL,
) ON [PRIMARY]
insert into superhero([description])
select '{
"universe": "dc",
"characters": [
{
"hero": "superman",
"alias": "clark kent",
"powers": [
"x-ray vision",
"super strength",
"super speed",
"laser eyes",
"bullet proof"
],
"friends": [
{
"hero": "batman",
"alias": "bruce wayne"
},
{
"hero": "cyborg",
"alias": "victor stone"
}
]
}
]
}'
Обратите внимание, что в примере JSON идет несколько уровней глубины. В иерархии обратите внимание на друзья внутри символов и символов находится внутри объекта ROOT. Это означает, что мне нужно будет запросить вложенные иерархические подмассивы JSON , чтобы преобразовать все данные JSON в табличный формат . Звучит сложнее, чем есть на самом деле!
Чтобы просмотреть таблицу, выполните простой SELECT *.
select * from superHero
Чтобы открыть JSON в столбце описания, используйте функцию OPENJSON:
select * from OPENJSON((select description from superHero where heroID = 1))
Поскольку JSON хранится в столбце Описание, я передал запрос функции OPENJSON, которая возвращает описание для heroID 1. Просматривая результаты, обратите внимание, что они помещены в ключ и значение, а также столбец тип. Тип относится к типу данных в столбце значение.
Обратите внимание, что ключ characters связан со значением, которое по-прежнему выглядит как JSON. Это потому, что OPENJSON открывает только один уровень за раз. Он только открыл уровень ROOT, который содержит вселенную и персонажей.
Использование пути JSON
Чтобы запросить значение символов напрямую, путь JSON можно передать в OPENJSON функция. Путь всегда начинается с $.
Помните, что массивы JSON используют индексирование массива с нуля. Это означает, что они начинаются с 0, а не с 1. Поэтому, если я хочу увидеть пары ключ-значение в символьном объекте, мне нужно передать позицию в путь. Поскольку я хочу видеть первую позицию, мой путь - ‘$ .characters [0]’
select *
from OPENJSON(
(select description from superHero where heroID = 1),
'$.characters[0]'
)
Используя тот же шаблон пути, если бы я хотел исследовать второго друга первого символа, путь был бы '$ .characters [0] .friends [ 1] '
Запрос вложенных иерархических субмассивов JSON
Указание пути к OPENJSON полезно для изучения отдельных уровней JSON, но не идеально для одновременного возврата нескольких вложенных иерархий. В SQL Server для выравнивания вложенного JSON можно использовать CROSS APPLY и OUTER APPLY. Хотя сначала это может показаться запутанным, SQL Server делает открытие иерархий JSON довольно интуитивно понятным.
Чтобы сгладить superHero JSON, начните с выбора из таблицы superHero. Затем ПЕРЕКРЕСТИТЕ ПРИМЕНИТЬ OPENJSON в поле описания.
select * from superHero s CROSS APPLY OPENJSON(s.description)
CROSS APPLY присоединяет каждую пару значений ключа к строке в superHero. Поскольку OPENJSON открывает корневой объект, двумя ключами являются universe и символы.
Используя ключевое слово WITH в запросе SQL, пару ключ-значение можно преобразовать в столбец, а не в строку:
select * from superHero s CROSS APPLY OPENJSON(s.description) WITH (universe varchar(10))
Обратите внимание, что возвращается только одна строка, и теперь она содержит столбец юниверс вместо ключевого столбца и столбца значений.
Чтобы открыть уровень вложенных символов, в операторе WITH преобразуйте символы как JSON. Затем добавьте в запрос еще один CROSS APPLY OPENJSON, открыв JSON.
select * from superHero s CROSS APPLY OPENJSON(s.description) WITH (universe varchar(10), characters nvarchar(max) as JSON) c CROSS apply OPENJSON(c.characters)
Чтобы сгладить символы, используйте WITH и вставьте ключи в символы. Сделайте то же самое с объектом друзей.
select heroID , universe , f.hero , f.alias , powers , g.hero as friendHero , g.alias as friendAlias from superHero s CROSS APPLY OPENJSON(s.description) WITH (universe varchar(10), characters nvarchar(max) as JSON) c CROSS apply OPENJSON(c.characters) WITH (hero varchar(20), alias varchar(20),powers nvarchar(max)as JSON, friends nvarchar(max)as JSON) f OUTER APPLY OPENJSON(f.friends) WITH(hero varchar(20), alias varchar(20)) g
Запрос выбирает поля из таблицы superHero и перекрестно объединяет их с объектами ROOT. Используя оператор WITH, запрос знает, что нужно выбрать значение из предоставленного ключа. В запросе я выбрал universe из вызова OPENJSON для корневого объекта. Преобразование значения символов в формат AS JSON позволяет передать его в другой OPENJSON. Объединив вместе OPENJSON с помощью CROSS APPLY и OUTER APPLY, запрос анализирует каждую из вложенных иерархий и возвращает сведенные данные в табличном формате.
Поздравляю! Вы только что преобразовали вложенный JSON в плоский набор результатов!
Последние мысли
Поскольку JSON используется все больше и больше из-за популярности JavaScript, понимание того, как с ним работать, является фантастическим навыком. Хотя это могло показаться устрашающим, работа с JSON в SQL Server довольно проста благодаря функции OPENJSON. В нескольких строках SQL вы можете преобразовать JSON в столбцы и строки. Для получения дополнительной информации об OPENJSON я рекомендую просмотреть официальную документацию Microsoft.
Благодарю вас!
- Если вам понравилась моя работа, подписывайтесь на меня на Medium, чтобы узнать больше!
- Получите ПОЛНЫЙ ДОСТУП и помогите поддержать мой контент, подписавшись!
- Давайте подключимся к LinkedIn
- Анализировать данные с помощью Python? Загляните на мой сайт!
