This behaviour is explained in the documentation - OPENJSON()
with explicit schema converts the values to the specified type.
When you specify a schema for the results by using the WITH clause of
the OPENJSON function, the function returns a table with only the
columns that you define in the WITH clause. In the optional WITH
clause, you specify a set of output columns, their types, and the
paths of the JSON source properties for each output value. OPENJSON
iterates through the array of JSON objects, reads the value on the
specified path for each column, and converts the value to the
specified type.
One possible solution here is the following statement, which defines Min
and Max
columns as nvarchar
columns:
DECLARE @FldKPRoleRequirementsList nvarchar(max) = N'
{
"OutPut":[
{"Task":"Pr","Min":"","Max":""},
{"Task":"ED","Min":"0","Max":""},
{"Task":"Dr","Min":"0","Max":"0"},
{"Task":"Pt","Min":"","Max":"0"},
{"Task":"Pr","Min":"1","Max":"0"}
]
}
'
SELECT
[Task],
TRY_CONVERT(int, NULLIF([Min], '')) AS [Min],
TRY_CONVERT(int, NULLIF([Max], '')) AS [Max]
FROM OPENJSON(@FldKPRoleRequirementsList, '$.OutPut') WITH (
[Task] nvarchar(1000) '$.Task',
[Min] nvarchar(1) '$.Min',
[Max] nvarchar(1) '$.Max'
)
Result:
---------------
Task Min Max
---------------
Pr
ED 0
Dr 0 0
Pt 0
Pr 1 0
""
?