Блог

Я пишу хранимую процедуру для вставки этих значений в таблицу. Всегда имя столбца моей таблицы будет ключом, а данные столбца будут значением в 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().

Пожалуйста, помогите мне с этим.

Я думаю, что динамический оператор должен быть вашим первым вариантом:

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"'
)
  

Я думаю, вы ищете что-то подобное. Это открывает и выравнивает 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
  

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