How to dynamically load SQL tables using OPENJSON

I have a json that looks like

[{
                "year": 2021,
                "state": "Nebraska",
                "report": "Farm Business Balance Sheet",
                "farmtype": "All Farms",
                "category": "Operator Age",
                "category_value": "45 to 54 years old",
                "category2": "All Farms",
                "category2_value": "TOTAL",
                "variable_id": "kount",
                "variable_name": "Farms",
                "variable_sequence": 10,
                "variable_level": 1,
                "variable_group": null,
                "variable_group_id": null,
                "variable_unit": "Number",
                "variable_description": "Estimated number of farms.",
                "variable_is_invalid": false,
                "estimate": 5889,
                "median": null,
                "statistic": "TOTAL",
                "rse": 0,
                "unreliable_estimate": 0,
                "decimal_display": 0
            },
            {
                "year": 2021,
                "state": "Nebraska",
                "report": "Farm Business Balance Sheet",
                "farmtype": "All Farms",
                "category": "Farm Typology",
                "category_value": "Off-farm occupation farms (2011 to present)",
                "category2": "All Farms",
                "category2_value": "TOTAL",
                "variable_id": "kount",
                "variable_name": "Farms",
                "variable_sequence": 10,
                "variable_level": 1,
                "variable_group": null,
                "variable_group_id": null,
                "variable_unit": "Number",
                "variable_description": "Estimated number of farms.",
                "variable_is_invalid": false,
                "estimate": 13398,
                "median": null,
                "statistic": "TOTAL",
                "rse": 0,
                "unreliable_estimate": 0,
                "decimal_display": 0
            }]

I am trying to use OPENJSON to dynamically insert the json into a table. To this I am doing something like this:

CREATE PROCEDURE load_proc 
@json NVARCHAR(MAX),
@table_name VARCHAR(50)
AS 
begin
declare @sql varchar(max)
set @sql= 'INSERT INTO [dbo].' + QUOTENAME(@table_name) + '(year_, state_,
report,farmtype,category,category_value,category2,category2_value,
variable_id,variable_name,variable_sequence,variable_level,
variable_group,variable_group_id,variable_unit,
variable_description,variable_is_invalid,estimate,median,statistic,
rse,unreliable_estimate,decimal_display) SELECT year_,
state_,
report,
farmtype,
category,
category_value,
category2,
category2_value,
variable_id,
variable_name,
variable_sequence,
variable_level,
variable_group,
variable_group_id,
variable_unit,
variable_description,
variable_is_invalid,
estimate,
median,
statistic,
rse,
unreliable_estimate,
decimal_display
        FROM OPENJSON('''+ @json +''',' + '''$'')
            WITH (
            year_ varchar(255) ' + '''$.year'',
state_ varchar(255) ' + '''$.state'',
report varchar(255) ' + '''$.report'',
farmtype varchar(255) ' + '''$.farmtype'',
category varchar(255) ' + '''$.category'',
category_value int ' + '''$.category_value'',
category2 varchar(255) ' + '''$.category2'',
category2_value int ' + '''$.category2_value'',
variable_id varchar(255) ' + '''$.variable_id'',
variable_name varchar(255) ' + '''$.variable_name'',
variable_sequence varchar(255) ' + '''$.variable_sequence'',
variable_level varchar(255) ' + '''$.variable_level'',
variable_group varchar(255) ' + '''$.variable_group'',
variable_group_id varchar(255) ' + '''$.variable_group_id'',
variable_unit varchar(255) ' + '''$.variable_unit'',
variable_description varchar(255) ' + '''$.variable_description'',
variable_is_invalid varchar(255) ' + '''$.variable_is_invalid'',
estimate int ' + '''$.estimate'',
median int ' + '''$.median'',
statistic varchar(255) ' + '''$.statistic'',
rse int ' + '''$.rse'',
unreliable_estimate int ' + '''$.unreliable_estimate'',
decimal_display int ' + '''$.decimal_display'')'
execute(@sql)
end

Unfortunately this does not work for me, so I investigated further by running

SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

Which gave me

Order   |   key      |   value   
0       |   year     |   2021    
0       |   state    |   Nebraska
0       |   report   |   Farm Business Balance Sheet
0       |   farmtype |   All Farms

and

select * from openjson(@json)

which gave me

key        |          value                             |         type
0             { "year": 2021, "state": "Nebraska"....               5
1.            { "year": 2021, "state": "Nebraska"...                5

So it seems that I need to adjust OPENJSON('''+ @json +''',' + '''$'') part of the SP, but I am not sure how it should be formatted. Anyone have any guidance?

Solution

Your problem is here:

category_value int ' + '''$.category_value'',
category2 varchar(255) ' + '''$.category2'',
category2_value int ' + '''$.category2_value'',

You have declared category_value and category2_value as int, however in your JSON you have string values for those:

"category_value": "45 to 54 years old",
"category2": "All Farms",
"category2_value": "TOTAL",

which causes the OPENJSON to fail.

Correcting the definition of those fields (to e.g. varchar(255)) will make your code work.

Demo on dbfiddle.uk