Сопоставьте КЛЮЧ JSON как имена столбцов в ЗАПРОСЕ INSERT

#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 системную функцию, поэтому я использовал довольно стандартный трюк для объединения имен столбцов в строку.