SQL SERVER – Understanding JSON Use is Case-Sensitive

SQL
15 Comments

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:

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

Solarwinds
, , ,
Previous Post
SQL SERVER – AlwaysOn AG (Availability Group) and TDE Error – Please Create a Master Key
Next Post
SQL SERVER – Beautify SQL Code with dbForge SQL Complete

Related Posts

15 Comments. Leave new

  • bryant law (@bryantlawmy)
    January 19, 2017 11:28 am

    SELECT JSON_VALUE(@myjson, ‘$.Name’) – N-Capital letter?

    Reply
  • 1st statement has case sensitive issue.
    2nd has invalid property issue.

    Reply
    • I already mentioned that in the blog. My question was “How will you know it was an invalid property or a case sensitive problem” ?

      Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • Mudit Agarwal
    May 14, 2017 5:53 pm

    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

    Reply
  • 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’

    Reply

Leave a Reply

Menu