CODEWITHSUNDEEP
.netjsonsql-server
ReaL_HyDRA
ReaL_HyDRA

Reputation: 365

OPENJSON in SQL server returning values stored as "" into zeros

The JSON I need to parse:

{
"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"}
]
}

This is where I called OPENJSON():

INSERT INTO RoleMinMaxTemp  
    SELECT *  FROM OPENJSON(@FldKPRoleRequirementsList,'$.OutPut')    
        WITH (  
       Role nvarchar(1000) '$.Role',  
       Min INT '$.Min',  
       Max INT '$.Max'  
         )  
    END

When displayed the "" is replaced by 0. How can I avoid that?

Upvotes: 0

Views: 330

Answers (1)

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 2

Related Questions