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

added 61 characters in body

Source Link

edited Mar 14, 2020 at 14:36

29.6k
6
30
54

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

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:

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

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

edited body

Source Link

edited Feb 3, 2020 at 6:50

Zhorov

29.6k
6
30
54

This behaviour is explained in the documantationdocumentation - 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:

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] varcharnvarchar(1) '$.Min',  
   [Max] varcharnvarchar(1) '$.Max'  
)

Result:

---------------
Task    Min Max
---------------
Pr      
ED      0   
Dr      0   0
Pt          0
Pr      1   0

This behaviour is explained in the documantation - 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:

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] varchar(1) '$.Min',  
   [Max] varchar(1) '$.Max'  
)

Result:

---------------
Task    Min Max
---------------
Pr      
ED      0   
Dr      0   0
Pt          0
Pr      1   0

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:

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

added 21 characters in body

Source Link

edited Jan 31, 2020 at 13:10

Zhorov

29.6k
6
30
54

This behaviour is explained in the documantation - 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:

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] varchar(1) '$.Min',  
   [Max] varchar(1) '$.Max'  
)

Result:

---------------
Task    Min Max
---------------
Pr      
ED      0   
Dr      0   0
Pt          0
Pr      1   0

This behaviour is explained in the documantation - OPENJSON() 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:

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] varchar(1) '$.Min',  
   [Max] varchar(1) '$.Max'  
)

Result:

---------------
Task    Min Max
---------------
Pr      
ED      0   
Dr      0   0
Pt          0
Pr      1   0

This behaviour is explained in the documantation - 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:

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] varchar(1) '$.Min',  
   [Max] varchar(1) '$.Max'  
)

Result:

---------------
Task    Min Max
---------------
Pr      
ED      0   
Dr      0   0
Pt          0
Pr      1   0

deleted 7 characters in body

Source Link

edited Jan 31, 2020 at 7:37

Zhorov

29.6k
6
30
54

Loading

Source Link

created Jan 31, 2020 at 7:16

Zhorov

29.6k
6
30
54

Loading

Collectives™ on Stack Overflow

Return to Answer