#sql #json #sql-server #stored-procedures
#sql #json #sql-сервер #хранимые процедуры
Вопрос:
Я пишу хранимую процедуру для вставки этих значений в таблицу. Всегда имя столбца моей таблицы будет ключом, а данные столбца будут значением в JSON.
DECLARE @json NVARCHAR(MAX) = N'[
{
"name": "abcd",
"id": 12,
"feelings": {
"happy": 0,
"angry": 1,
"sad": 1
}
},
{
"name": "abcdf",
"id": 14,
"feelings": {
"happy": 0,
"angry": 1,
"sad": 1
}
}
]'
DECLARE @id INT;
DECLARE @last_name NVARCHAR(50);
SET @id =10;
SET @last_name = 'Mike'
Пример:
INSERT INTO Table([name],[id],[lastname],[happy],[angry],[sad])
VALUES ("abcd",@id,@last_name,0,1,1)
How can I achieve this for any JSON format? Always the JSON key should be mapped to the column name. The insert statement should be able to insert all the entries from the JSON. Only the key inside the feelings will change. All others remain the same.
Мне нужно включить данные из JSON, а также статические объявленные данные в запрос insert.Позволяет ли OPENJSON включать статические данные в WITH().
Пожалуйста, помогите мне с этим.
Комментарии:
1. Всегда ли в таблице есть эти два столбца? В качестве примечания, входной JSON
[{"key1":"name1","key2",:"name2"}]
недопустим (,
после"key2"
).2.
INSERT
инструкции должны быть четко определены. По сути, у вас не может быть инструкции, в которой говорится «Вставить значения элементов в столбцы с одинаковыми именами»; SQL работает не так. Вам нужно будет написать SQL для каждой таблицы, которая считывает соответствующий JSON. В противном случае вам нужно будет пройти по маршруту динамического SQL, который я не предлагаю для тех, кто не знаком с SQL и не понимает предостережений, связанных с работой с динамическими операторами.3. Вы уже ознакомились с документацией по теме ? Что вы пробовали сами? (PS Ни данные JSON, ни код SQL в вашем вопросе в настоящее время не имеют допустимого синтаксиса.)
4. Изменяются ли КЛЮЧЕВЫЕ поля внутри объекта feeling? Если это так, измените его на массив. Остальные столбцы соответствуют именам полей документа JSON, поэтому они должны быть преобразованы в столбцы, определенные с помощью предложения WITH OPENJSON
Ответ №1:
Я думаю, что динамический оператор должен быть вашим первым вариантом:
JSON:
DECLARE @json nvarchar(max) = N'[
{
"name": "abcd",
"id": 12,
"feelings": {
"happy": 0,
"angry": 1,
"sad": 1
}
},
{
"name": "abcdf",
"id": 14,
"feelings": {
"happy": 0,
"angry": 1,
"sad": 1
}
}
]'
Инструкция:
DECLARE @stm nvarchar(max)
DECLARE @columns nvarchar(max)
DECLARE @schema nvarchar(max)
DECLARE @id int
SET @id = 10
SELECT
@columns = STRING_AGG(QUOTENAME([key]), N','),
@schema = STRING_AGG(CONCAT(QUOTENAME([key]), N' int ''$.feelings."', [key], '"'''), N',')
FROM OPENJSON(@json, '$[0].feelings')
SELECT @stm = CONCAT(
N'INSERT INTO InputTable ([id],[name],',
@columns,
N') SELECT @id,[name],',
@columns,
N' FROM OPENJSON(@json) WITH ([name] varchar(100) ''$."name"'',',
@schema,
N')'
)
PRINT @stm
EXEC sp_executesql @stm, N'@json nvarchar(max), @id int', @json, @id
Сгенерированный динамический оператор:
INSERT INTO InputTable ([id],[name],[happy],[angry],[sad])
SELECT @id,[name],[happy],[angry],[sad]
FROM OPENJSON(@json) WITH (
[name] varchar(100) '$."name"',
[happy] int '$.feelings."happy"',
[angry] int '$.feelings."angry"',
[sad] int '$.feelings."sad"'
)
Комментарии:
1. Я не знаю определений столбцов.. Определения столбцов могут меняться, и в соответствии с этим JSON также изменится. Есть ли какой-либо способ извлечь имя ключа из JSON для хранимой процедуры?
2. версия 12.0.It База данных Microsoft Azure SQL
3. [{ «имя»: 3, «идентификатор»: 4393, «чувства»: { «счастливый»: 0, «сердитый»: 1, «грустный»: 1 } },{},{}] В этом JSON изменятся только записи JSON feeling, и оператор insert должен включать только name и feeling. Пример: вставьте в таблицу значения (name, happy, angry) («abcd», «yes», «да», «да»).@ Жоров . Как исключить некоторые ключи?
4. Выполнено. Пожалуйста, помогите мне в том же самом
5. Можно ли включить статическое значение в WITH() из OPENJSON? У меня установлено значение переменной @var1 = 20. Мне нужно включить это также в качестве столбца в инструкцию insert
Ответ №2:
Я думаю, вы ищете что-то подобное. Это открывает и выравнивает JSON
declare
@json nvarchar(max)=N'[
{
"name": "abcd",
"id": 12,
"feelings": {
"happy": 0,
"angry": 1,
"sad": 1
}
},
{
"name": "abcdf",
"id": 14,
"feelings": {
"happy": 0,
"angry": 1,
"sad": 1
}
}
]';
INSERT INTO Table([name],[happy],[angry],[sad])
select oj.[name], f.*
from openjson(@json) with (name nvarchar(4000),
id int,
feelings nvarchar(max) as json) oj
cross apply
openjson(oj.feelings) with (happy int,
angry int,
sad int) f;
Результаты
name id happy angry sad
abcd 12 0 1 1
abcdf 14 0 1 1
Ответ №3:
Не уверен, что я правильно понял ваш вопрос, но если вам нужно получить данные из JSON на основе столбцов, существующих в таблице, которые вы хотите передать по имени, тогда вам нужен динамический sql. Итак, вот пример того, как вы можете сделать это с помощью sys.columns
view:
drop table if exists dbo.temp_data
create table dbo.temp_data (name nvarchar(128), happy bit, angry bit, sad bit);
----------------------------------------------------------------------------------------------------
declare @table_name nvarchar(256) = 'dbo.temp_data'
declare @data nvarchar(max) = '[
{
"name": "abcd",
"id": 12,
"feelings": {
"happy": 0,
"angry": 1,
"sad": 1
}
},
{
"name": "abcdf",
"id": 14,
"feelings": {
"happy": 0,
"angry": 1,
"sad": 1
}
}
]';
declare @stmt nvarchar(max);
declare @stmt_part1 nvarchar(max);
declare @stmt_part2 nvarchar(max);
select
@stmt_part1 = concat(isnull(@stmt_part1 ',', ''),c.name),
@stmt_part2 = concat(isnull(@stmt_part2 ',', ''),'json_value(o.value, ''$.feelings.',c.name,''')')
from sys.columns as c
where
c.object_id = object_id(@table_name) and
c.name <> 'name'
set @stmt = concat('
insert into ',@table_name,' (name,',@stmt_part1,')
select
json_value(o.value, ''$.name'') as name,',@stmt_part2,'
from openjson(@data) as o
');
exec sys.sp_executesql
@stmt,
N'@data nvarchar(max)',
@data = @data;
----------------------------------------------------------------------------------------------------
select * from dbo.temp_data
Я не знаю, какую версию Sql Server вы используете, поэтому я не уверен, сможете ли вы использовать string_agg
системную функцию, поэтому я использовал довольно стандартный трюк для объединения имен столбцов в строку.