CODEWITHSUNDEEP
sqljsonsql-serverstored-procedures
user13093893
user13093893

Reputation:

Map JSON KEY as column names in INSERT QUERY

I am writing a stored procedure to insert these values into a table. Always my table column name will be the key and column data will be the value in the 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'

Example:

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.

I need to include data from the JSON as well as static declared data in the insert query.Does OPENJSON allow including static data in WITH().

Please help me with this.

Upvotes: 0

Views: 3404

Answers (3)

Zhorov
Zhorov

Reputation: 29943

I think that dynamic statement should be your first option:

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
        }
      }
]'

Statement:

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

Generated dynamic statement:

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

Upvotes: 2

roman
roman

Reputation: 117345

Not sure I got your question correctly, but if you need to get data from JSON based on columns existing in table which you want to pass by name then you need to have dynamic sql. So here's an example of how you can do it using 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

I don't know which version of Sql Server you're using so I'm not sure if you can use string_agg system function so I've used pretty standard trick to aggregate column names into string.

Upvotes: -1

SteveC
SteveC

Reputation: 6015

I think you're looking for something like this. This opens and flattens the 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;

Results

name    id  happy   angry   sad
abcd    12  0       1       1
abcdf   14  0       1       1

Upvotes: 1

Related Questions