How can I read a long-valued property from a JSON string?
I have a JSON object that has one of the properties that has a very long value. When I try to retrieve this value with JSON_VALUE()
, it returns null.
declare @json nvarchar(max) =
'
{
"AVeryLongValue": "Founded in 2008, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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 have verified the use is_json(@json)
, JSON is valid. The long lasting value doesn't seem to have special characters, etc.
If I truncate the value, I get the value as I expect, so the code looks fine. For example:
declare @json nvarchar(max) = '
{
"AVeryLongValue": "Founded in 2008"
}
'
select json_value(@json, '$.AVeryLongValue') as 'AVeryLongValue'
Results:
AVeryLongValue
------------
Founded in 2008
Questions:
- Why am I getting null when reading a long value? Is there any special treatment needed?
- I have a mix of JSON objects with long and short values for this property. Also sometimes the property is missing at all, so JSON_VALUE () returns null for those objects. Any advice on how to apply conditional special handling only to objects for which this property has lasting value?
+3
source to share
2 answers
Why am I getting null when reading a long value?
Since the value was more than 4000 characters, it json_value()
doesn't handle this. Here is the MSDN link
Return value
Returns a single text value of type nvarchar (4000).
If the value is more than 4000 characters:
In lax mode, JSON_VALUE returns null. In strict mode, JSON_VALUE returns an error.
Is there any special treatment needed? How can I get around this limitation?
Found this great video of SQL Server 2016 and JSON support on Channel9 which mentions this issue 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 at all, so JSON_VALUE () returns null for those objects. Any advice on how to apply conditional special handling only to objects for which this property has lasting value?
Using 'strict' path mode in the JSON expression supplied json_value()
can do json_value()
to throw an error if the property value is long and will be truncated.
declare @json nvarchar(max) =
'
{
"AVeryLongValue": "Founded in 2008, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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 in
Msg 13625, Level 16, State 1, Line 13
String value in the specified JSON path would be truncated.
Also, if the property is not present in the JSON, it will throw an error, for example:
Msg 13608, Level 16, State 5, Line 12
Property cannot be found on the specified JSON path.
I can use a mode strict
along with logic try-catch
like this to handle a combination of objects.
declare @json nvarchar(max) =
'
{
"AVeryLongValue": "Founded in 2008, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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, Qaru 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 Qaru each month to help solve coding problems, develop new skills, and find job opportunities. Qaru 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
+2
source to share
I may have found a way to manipulate a value greater than 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 is better to use the WITH clause , on the Sql server blog, it was said to have better performance. Blog post
0
source to share