How to insert JSON to an existing table without specifying column names?

Question

To a table with two columns, named Id and Name, how can I insert the following json as rows, but without explicitly specifying the column names of the table (i.e. without WITH (Id ..., Name ...)?

[
    {
        "Id": "f08af9c2-8e67-4a7f-9413-1afffa2de96b",
        "SomeOtherKey": " ... ",
        "Name": "The One",
        ...
    },
    {
        "Name": "All is one"
        "Id": "9bbb094b-aa64-4c36-90a2-50e10f91c6a3",
        "Whatever": 99,
        ...
    },

    {       
        "Id": "af9d22d8-1e46-4d57-8179-75f094d2efa1",
        "SomeArrayWhyNot": [0, 1, 1, 2, 3, 5, 8, 13, 21]
        "Surprise": "This one does not have a Name value!!! 😱"
        ...
    },

    ...
]

The question is basically how to make SQL match the key-name to its suitable column name, ignoring json values with keys that do not have suitable column names, resulting with the following table (for the above json example):

Id	Name
f08af9c2-8e67-4a7f-9413-1afffa2de96b	The One
9bbb094b-aa64-4c36-90a2-50e10f91c6a3	All is one
af9d22d8-1e46-4d57-8179-75f094d2efa1	NULL
...	...

Answer 1

Not sure why you want this, but you can also do this:

INSERT INTO YourTable (Id, Name)
SELECT JSON_VALUE(x.value, '$.Id'), JSON_VALUE(x.value, '$.Name')
FROM OPENJSON('[{
        "Id": "f08af9c2-8e67-4a7f-9413-1afffa2de96b",
        "SomeOtherKey": " ... ",
        "Name": "The One"
    },
    {
        "Name": "All is one",
        "Id": "9bbb094b-aa64-4c36-90a2-50e10f91c6a3",
        "Whatever": 99
    },
    {       
        "Id": "af9d22d8-1e46-4d57-8179-75f094d2efa1",
        "SomeArrayWhyNot": [0, 1, 1, 2, 3, 5, 8, 13, 21],
        "Surprise": "This one doesn''t have a Name value!!! 😱"
    }]') x

EDIT dynamic version. It has many caveats though, your column must match exactly the case in the json, it's very longwinded but if that's what floats your vessel...

declare @json nvarchar(max) = '[{
        "Id": "f08af9c2-8e67-4a7f-9413-1afffa2de96b",
        "SomeOtherKey": " ... ",
        "Name": "The One"
    },
    {
        "Name": "All is one",
        "Id": "9bbb094b-aa64-4c36-90a2-50e10f91c6a3",
        "Whatever": 99
    },
    {       
        "Id": "af9d22d8-1e46-4d57-8179-75f094d2efa1",
        "SomeArrayWhyNot": [0, 1, 1, 2, 3, 5, 8, 13, 21],
        "Surprise": "This one doesn''t have a Name value!!! 😱"
    }]'

create table YourTable (Id nvarchar(100), Name NVARCHAR(MAX))
create table #cols (name sysname, row_id int identity)
insert into #cols (name)
  select STRING_ESCAPE(name, 'json')
  from sys.columns sc
  where sc.object_id = OBJECT_ID('YourTable')

DECLARE @sql nvarchar(max)

set @sql = N'
INSERT INTO YourTable ([COLS])
SELECT [JSONS]
FROM OPENJSON(@json) x'

SET @sql = REPLACE(@sql, '[COLS]', 
  STUFF((select ',' + QUOTENAME(name) from #cols order by row_id for xml Path(''), type).value('.', 'nvarchar(max)'), 1, 1, ''))

set @sql = replace(@sql, '[JSONS]', 
  stuff((SELECT ', JSON_VALUE(x.value, ''$."' + REPLACE(name,'''', '''''') + '"'')' 
    from #cols order by row_id for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, ''))
exec sp_executesql @sql, N'@json nvarchar(max)', @json = @json

select * FROM YourTable