How to read a property with a long value from JSON string?

Question

I have a JSON object where one of the properties has a very long value. When I try to extract this value using JSON_VALUE(), it returns 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'

Results:

AVeryLongValue
--------
NULL

I confirmed using is_json(@json), the JSON is valid. The long value does not seem to have any special characters etc.

If I shorten the value, I do get the value as I expect, so the code seems to be ok. For example:

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

Results:

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

Questions:

1. Why do I get a null when reading a long value? Does it need any special handling?
2. I have a mix of JSON objects with long and short values for this property. Also sometimes the property is missing altogether, so JSON_VALUE() would return null for those objects. Any recommendation how to apply conditional special handling only for the objects that this property as long value?

Answer 1

Why do I get a null when reading a long value?

Because the value was longer than 4000 characters and json_value() cannot handle it. Here is the 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.

Does it need any special handling? How do I work around this limitation?

Found this great video SQL Server 2016 and JSON Support on Channel9, which mentions of this problem at 15:00 and suggests a workaround at 25:00

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

I have a mix of JSON objects with long and short values for this property. Also sometimes the property is missing altogether, so JSON_VALUE() would return null for those objects. Any recommendation how to apply conditional special handling only for the objects that this property as long value?

The use of 'strict' path mode in the JSON expression provided to json_value(), can make json_value() to throw an error if the property value is long and would be truncated.

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')

results into

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

Also if the property is missing from the JSON, it would result in an error like:

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

I can use the strict mode along with try-catch logic like this to handle the mix of objects.

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

Answer 2

I might have found a way to manage a value bigger than the JSON_Value nvarchar (4000).

Use the WITH clause:

DECLARE @jsonInfo NVARCHAR(MAX)
SET @jsonInfo=N'{
    "Items": [ {
            "guid": "f0acb160-62eb-4622-bef2-bb9e8afe7314",
            "timestamp": "2017-10-31T23:59:51.093Z",
            "data": {
                "LongText": "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas venenatis vel orci a tincidunt. Maecenas suscipit sed neque non mollis. Proin nec mollis lacus, in gravida lorem. Nullam pharetra urna vitae consectetur placerat. Proin rutrum risus vitae nunc mattis, nec pellentesque ligula viverra. Maecenas at semper libero. Curabitur porta urna vel arcu interdum scelerisque. Cras ultrices tincidunt pulvinar. Nam molestie aliquet sapien nec ultricies. Nulla facilisis quam a ante lobortis tincidunt. Morbi ac nulla mi.Morbi mattis in ex non ullamcorper. Etiam sit amet lacus eros. Suspendisse ac magna purus. Quisque vel lectus a libero posuere condimentum ac posuere orci. Fusce vel molestie velit. Nunc vulputate mauris consequat massa imperdiet, in aliquet metus dignissim. Donec posuere odio suscipit, sollicitudin tellus nec, ultricies augue. Duis vel lacinia massa. Nam venenatis mauris ut risus pharetra bibendum. Phasellus ornare enim eu enim mattis efficitur. Cras in arcu leo. Maecenas nec tempus mauris. Nulla vel tortor non elit elementum maximus. Nulla eu ex neque. Duis nisi odio, finibus a dui vel, sodales venenatis leo. Pellentesque fermentum rutrum pellentesque.Nullam euismod nulla lectus, a blandit nisi viverra non. Aliquam ut auctor turpis. Orci varius natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Vestibulum id ante scelerisque, aliquet ipsum interdum, dapibus diam. Fusce faucibus orci ut eros aliquet finibus. Maecenas magna turpis, pulvinar a eleifend sed, gravida eget massa. Praesent laoreet convallis condimentum. Aenean rutrum leo id malesuada fermentum. Nullam quis ullamcorper ipsum, non imperdiet mi. Cras ex neque, molestie maximus rutrum sit amet, rhoncus quis justo. Cras venenatis consequat justo sed cursus. Phasellus mollis malesuada interdum. Aliquam erat volutpat. Curabitur ultricies dapibus ante sit amet convallis.Quisque massa mi, mattis sed semper eu, rutrum ut massa. Nam velit enim, placerat id placerat et, aliquam vel purus. Nulla non auctor eros. Nullam sed elit metus. Donec tempor tempus sagittis. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Nunc elementum gravida auctor. Aenean posuere pretium consequat.Orci varius natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. In eu finibus ligula, a blandit felis. Cras interdum sapien ac urna fringilla ornare. In consequat lectus luctus tellus tempus pellentesque. Duis vitae turpis tincidunt, tincidunt mauris at, vehicula ligula. Curabitur sed tortor lacinia sapien varius tincidunt vitae nec est. Duis pulvinar eros lorem, id luctus justo convallis tempor. Pellentesque sit amet eros a magna condimentum hendrerit non vitae enim.Pellentesque sit amet ex vel felis auctor luctus ultricies ac mauris. Vivamus tempus sit amet ipsum vitae hendrerit. Vivamus dignissim blandit magna tempor pharetra. Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Nam tempus tristique justo egestas tempor. Nulla lacus tortor, interdum ac ante pulvinar, ultricies mollis ligula. Donec facilisis blandit justo, ac vulputate urna tempor vel. Curabitur commodo ex et fringilla pellentesque. Donec lobortis lorem vel ante rhoncus posuere. Suspendisse cursus velit sit amet nisi sagittis tristique. Ut nec accumsan risus, id tincidunt ipsum. Vivamus convallis sodales tellus vel ultricies.Nam semper risus ligula, eu venenatis ipsum fringilla quis. Sed cursus pellentesque ex eu finibus. Morbi ligula lectus, semper et laoreet id, porttitor quis sapien. Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Nulla a feugiat est, vel faucibus arcu. Vivamus molestie tortor a augue maximus vulputate. Aliquam at nulla nibh. Nullam blandit quam sed tortor gravida, nec scelerisque felis interdum.Cras et interdum elit, quis laoreet sapien. Integer arcu dui, interdum eu egestas placerat, posuere a posuere."
            }
        }
    ]
}'

-- doesnt work because of the JSON_Value nvarchar (4000)
 SELECT 
 JSON_Value (i.value, '$.guid') as [GUID],
  CONVERT(datetime, JSON_Value (i.value, '$.timestamp')) as [TimeStamp],
  JSON_Value (i.value, '$.data.LongText') as LongText
FROM OPENJSON (@jsonInfo, '$.Items') as i

-- workaround
select 
i.*
FROM OPENJSON (@jsonInfo, '$.Items')
WITH ([GUID]   nvarchar(40) '$.guid' , 
    [TimeStamp]   Datetime '$.timestamp',  
      LongText nvarchar(MAX) '$.data.LongText'
 )   as i

By default I think it's better to use the WITH Clause, on The Sql server blog, it was said it had better performance. Blog Post