Apples are case sensitive, oranges are not

 Amazing discovery of the day: JSON parsing in SQL Server is kind of case sensitive.

More specifically, when you write

select * from openjson(SomeJSON) with (Foo int, Bar varchar(max))

the names Foo and Bar are matched against the JSON in a case sensitive manner. If the JSON contains foo instead, you will get a field called Foo with a uniform NULL in the recordset.

The path argument of JSON_VALUE/JSON_QUERY acts the same way. The following query returns NULL:

select json_value('{"a": 1}', '$.A')

Even the key column that OPENJSON returns is listed with a case sensitive collation. The following also returns NULL:

select value from openjson('{"a":1}') where [key]='A'

To get to the value in a case insensitive manner, override the collation. Like this:

select value from openjson('{"a":1}') where [key]='A' collate SQL_Latin1_General_CP1_CI_AS

This query returns a 1.