sql - 如何从 JSON 字符串读取具有长值的属性？

Question

我有一个 JSON 对象，其中一个属性具有很长的值。当我尝试使用 JSON_VALUE() 提取此值时，它返回 null。

declare @json nvarchar(max) =
'
{
    "AVeryLongValue": "Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here."
}
'
select json_value(@json, '$.AVeryLongValue') as 'AVeryLongValue'

结果:

AVeryLongValue
--------
NULL

我使用is_json(@json)确认，JSON 是有效的。 long值似乎没有任何特殊字符等。

如果我缩短该值，我确实会得到预期的值，因此代码似乎没问题。例如:

declare @json nvarchar(max) = '
{
    "AVeryLongValue": "Founded in 2008"
}
'
select json_value(@json, '$.AVeryLongValue') as 'AVeryLongValue'

结果:

AVeryLongValue
------------
Founded in 2008

问题:

1. 为什么读取 long 值时会得到 null？需要特殊处理吗？
2. 我有一个 JSON 对象的组合，该属性具有长值和短值。有时该属性完全丢失，因此 JSON_VALUE() 将为这些对象返回 null。有什么建议如何仅对该属性作为长值的对象应用条件特殊处理？

Answer 1

为什么读取长值时会得到 null？

因为该值超过 4000 个字符，并且 json_value() 无法处理它。这是MSDN reference

Return Value

Returns a single text value of type nvarchar(4000).

If the value is greater than 4000 characters:

In lax mode, JSON_VALUE returns null.

In strict mode, JSON_VALUE returns an error.

需要任何特殊处理吗？如何解决此限制？

发现了这个很棒的视频SQL Server 2016 and JSON Support on Channel9 ，其中在 15:00 提到了此问题并在 25:00 建议了解决方法

select value from openjson(@json) where[key] = 'AVeryLongValue'

我有一个 JSON 对象的组合，该属性具有长值和短值。有时该属性完全丢失，因此 JSON_VALUE() 将为这些对象返回 null。有什么建议如何仅对该属性作为长值的对象应用条件特殊处理？

使用'strict' path mode在提供给 json_value() 的 JSON 表达式中，如果属性值很长并且会被截断，可以使 json_value() 抛出错误。

declare @json nvarchar(max) =
'
{
    "AVeryLongValue": "Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here."
}
'
declare @AVeryLongValue nvarchar(max)
select @AVeryLongValue = json_value(@json, 'strict $.AVeryLongValue')

结果

Msg 13625, Level 16, State 1, Line 13
String value in the specified JSON path would be truncated.

此外，如果 JSON 中缺少该属性，则会导致如下错误:

Msg 13608, Level 16, State 5, Line 12
Property cannot be found on the specified JSON path.

我可以使用 strict 模式以及像这样的 try-catch 逻辑来处理对象的混合。

declare @json nvarchar(max) =
'
{
    "AVeryLongValue": "Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here. Founded in 2008, Stack Overflow is the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. More than 50 million professional and aspiring programmers visit Stack Overflow each month to help solve coding problems, develop new skills, and find job opportunities. Stack Overflow partners with businesses to help them understand, hire, engage, and enable the worlds developers. Our products and services are focused on developer marketing, technical recruiting, market research, and enterprise knowledge sharing. Learn more about our business solutions here."
}
'

declare @AVeryLongValue nvarchar(max)
begin try
    select @AVeryLongValue = json_value(@json, 'strict $.AVeryLongValue') 
end try
begin catch
    select 'In catch section'
    select  @AVeryLongValue = value from openjson(@json) where[key] = 'AVeryLongValue'
end catch
if @AVeryLongValue is null
    select 'The JSON did not have property AVeryLongValue'
else
    select @AVeryLongValue