#json #sql-server #tsql #parsing #open-json
Вопрос:
Как извлечь значения из employment_types
массивов ( type
, salary
) и skills
( name
, level
) и отобразить их в столбцах? Я пробовал, employment_types
и это не работает, не говоря уже о skills
:
declare @json nvarchar(max)
set @json = '[
{
"title": "IT Admin",
"experience_level": "mid",
"employment_types": [
{
"type": "permanent",
"salary": null
}
],
"skills": [
{
"name": "Security",
"level": 3
},
{
"name": "WIFI",
"level": 3
},
{
"name": "switching",
"level": 3
}
]
},
{
"title": "Lead QA Engineer",
"experience_level": "mid",
"employment_types": [
{
"type": "permanent",
"salary": {
"from": 7000,
"to": 13000,
"currency": "pln"
}
}
],
"skills": [
{
"name": "Embedded C",
"level": 4
},
{
"name": "Quality Assurance",
"level": 4
},
{
"name": "C ",
"level": 4
}
]
}
]';
SELECT *
FROM OPENJSON(@JSON, '$.employment_types')
WITH
(
type nvarchar(50) '$.type',
salary varchar(max) '$.salary'
)
Существует почти 7000 записей, и я хотел бы показать упомянутые выше столбцы из всех них.
Комментарии:
1. Каков ваш ожидаемый результат для этого образца, учитывая, что их может быть несколько
employment_types
и несколькоskills
?
Ответ №1:
Трудно точно знать, чего вы хотите, учитывая, что оба employment_types
и skills
являются массивами. Но, предполагая employment_types
, что всегда есть только один элемент, вы могли бы сделать что-то вроде этого
SELECT
j1.title,
j1.experience_level,
j1.employment_type,
salary = j1.salary_currency ' ' CONCAT(j1.salary_from, ' - ', j1.salary_to),
j2.name,
j2.level
FROM OPENJSON(@JSON)
WITH (
title nvarchar(100),
experience_level nvarchar(10),
employment_type nvarchar(50) '$.employment_types[0].type',
salary_from int '$.employment_types[0].salary.from',
salary_to int '$.employment_types[0].salary.to',
salary_currency char(3) '$.employment_types[0].salary.currency',
skills nvarchar(max) AS JSON
) j1
CROSS APPLY OPENJSON(j1.skills)
WITH
(
name nvarchar(50),
level int
) j2
- Поскольку мы извлекаем данные непосредственно из корневого объекта, нам не нужен аргумент пути JSON.
OPENJSON
будет автоматически разбивать массив на отдельные строки. Если бы вы просто хотелиemployment_types
, вы могли бы перейти непосредственно к этому с аргументом пути. employment_types[0]
означает получить только первый элемент массива. Если вам нужны все элементы, вам понадобится еще одинOPENJSON
- Обратите внимание на использование
AS JSON
forskills
, это означает, что весь массив JSON извлекается, а затем может быть передан через другой вызов вOPENJSON
Комментарии:
1. Спасибо, я действительно ценю ваши усилия, но у меня есть еще один вопрос. Прежде чем задать свой вопрос здесь, я просмотрел несколько похожих вопросов/руководств, и в подавляющем большинстве из них они используют параметр JSONPath в OPENJSON, как я сделал в своем коде (OPENJSON(@JSON,’$. employment_types’)). Мне любопытно, почему это не работает и как работает этот нуль в скобках в вашем коде. Просто нужно дополнительное объяснение по этому поводу.
2. Хорошо, я дал вам немного больше объяснений.