The things I get to learn from my daughter is amazing. I think all of us need to constantly explore without any prejudice and set biases. Whenever I get a new toy for some occasion, I see there is at least a half hour of exploration to understand how the toy works and she goes into a whole new world of searching new ways to play. This makes the whole experience worth it. Let us learn about JSON.
This is almost the same experience I go through working with SQL Server. With SQL Server 2016 introducing JSON, there are a few blogs that I can circle back on using this new capability already. But when you start using the same, the learnings can be multifold. Let us understand from the below code to what the potential value would be:
DECLARE @myjson NVARCHAR(MAX) =
'{
"Name": "Pinal",
"Surname": "Kumar",
"Birth": {"DOB":"2000-12-12" , "Town":"Gandhi Nagar", "Country":"India"}
}'
SELECT JSON_VALUE(@myjson, '$.Name')As you can see, this is relatively simple code and I am trying to retrieve the value available in the “Name” property. This would return “Pinal” as shown below.
Having said that, if you make a simple mistake of even using a different case or even if you try to retrieve a property that is not existing, the output would be NULL.
SELECT JSON_VALUE(@myjson, '$.name')
SELECT JSON_VALUE(@myjson, '$.name123')
In the above example, both the statements will return you NULL. As I try to wrap up this blog, I want to see how you would solve the above problem? How will you know it was an invalid property or a case sensitive problem? Write it over comments and share your experience.
Reference: Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
SELECT JSON_VALUE(@myjson, ‘$.Name’) – N-Capital letter?
He’s not asking how to fix this problem by capitalizing the letter ‘n’, he’s asking how would you know that it was supposed to be a capital ‘N’ vs the attribute “Name” not being in the JSON at all.
Correct @Logan.
Yup.
1st statement has case sensitive issue.
2nd has invalid property issue.
I already mentioned that in the blog. My question was “How will you know it was an invalid property or a case sensitive problem” ?
JSON_VALUE() function has Two Properties.
1. Json_string
2. Json_path
With Json_path we can specify the path mode(lax or strict).
by default is “lax” path mode
if json_path is invalid or its not present in the json string then it returns NULL
but if we specify the path mode to “strict” , it will raise an error.
eg. SELECT JSON_VALUE(@myjson, ‘strict$.name123’)
Msg 13608, Level 16, State 1, Line 1
Property cannot be found in specified path.
Thanks for sharing @Sanket
I wish there was a way to make the path not be case sensitive. Here is the only way have been able to overcome this, and it has its drawbacks (loose case in the data). Would love to know if there is a better way…
JSON_VALUE( LOWER( @myjson ), ‘$.name’ )
and then I just always use all lower case on my path. But as I said, this looses the case fidelity of the data you are retrieving.
I wish the same.
As a developer who also does a lot of SQL work I wish the exact opposite. The JSON is a contract to which you have to agree. My wish would be that by default SQL installs were case sensitive. If I had a pound for every time some SQL broke when deployed into a customer’s case sensitive database or instance then I’d be a very rich man.
Good one Chris.
A little complex, but this could be a good solution:
DECLARE @myjson NVARCHAR(MAX) =
‘{
“Name”: “Pinal”,
“Surname”: “Kumar”,
“Birth”: {“DOB”:”2000-12-12″ , “Town”:”Gandhi Nagar”, “Country”:”India”}
}’
DECLARE @propertyName nvarchar(100);
SET @propertyName = ‘Name’
IF Exists(SELECT T.[PropertyName] from (SELECT @propertyName as [PropertyName]) T WHERE T.PropertyName in (SELECT [key] from OPENJSON(@myjson, ‘$’)))
BEGIN
declare @dyn_sql nvarchar(1000) = ‘SELECT JSON_VALUE(”’ + @myjson + ”’,”$.’ + @propertyName + ”’)’;
exec sp_executesql @dyn_sql ;
END
Else
BEGIN
SELECT ‘Bad Property name’ As [Wrong Property Name];
SELECT [key] as [Possible Property Names] from OPENJSON(@myjson, ‘$’);
END
Thanks for sharing.
May want to dump the key-value into a temp table where the collation is case insensitive.
SELECT [key] COLLATE SQL_Latin1_General_CP1_CI_AS AS KeyDesc,
[value] COLLATE SQL_Latin1_General_CP1_CI_AS AS ValueDesc
INTO #JsonObjKV
FROM OPENJSON(@JsonObj);
–Proof
SELECT * FROM #JsonObjKV WHERE KeyDesc = ‘xyz’
SELECT * FROM #JsonObjKV WHERE KeyDesc = ‘XYZ’