1

In T-Sql I am parsing JSON and using PIVOT.

Select * from (select [key],convert(varchar,[value])[value] 
from openjson ('{"Name":"tew","TabTypeId":9,"Type":3}'))A
    pivot(max(value) for [key] in ([Name],tabTypeId,[Type]))b

It is not treating tabTypeId as equal to TabTypeId. I am getting NULL for tabTypeId.
If I use TabTypeId I get the value 9.
Why is it happening?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Narendra
  • 11
  • 5

1 Answers1

6

It's not PIVOT that is case sensitive, it's the data returned from OPENJSON that is. If you check the data returned from it, you'll see that the column key is a binary collation:

SELECT name, system_type_name, collation_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT [key], CONVERT(varchar, [value]) AS [value] FROM OPENJSON(''{"Name":"tew","TabTypeId":9,"Type":3}'');',NULL,NULL)
name system_type_name collation_name
key nvarchar(4000) Latin1_General_BIN2
value varchar(30) SQL_Latin1_General_CP1_CI_AS

For binary collations the actual bytes of the characters must match. As such N'tabTypeId' and N'TabTypeId' are not equal as N'T' and N't' have the binary values 0x5400 and 0x7400.

Though I am unsure why you are using PIVOT at all; just define your columns in your OPENJSON call:

SELECT name, --Columns are intentionally demonstrating non-case sensitivity
       tabTypeId,
       type
FROM OPENJSON('{"Name":"tew","TabTypeId":9,"Type":3}')
        WITH (Name varchar(3),
              TabTypeId int,
              Type int);

Note that in the WITH clause of OPENJSON the column names are still case sensitive. tabTypeId int would also yield NULL. If you "had" to have a column called tabTypeId defined prior to the SELECT you would use tabTypeId int '$.TabTypeId' instead.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • ok Thank you. some jsons have TabTypeID and some tabtypeID. Even in with clause I need to specify the same case. How to overcome this. – Narendra Jun 01 '23 at 11:44
  • Fix the JSON, @Narendra , would be my first suggestion. Otherwise return columns for *both* `TabTypeId` and `tabTypeId` and then use an `ISNULL`/`COALESCE` in the `SELECT`. But, again, fixing the JSON is the *correct* solution in my opinion. – Thom A Jun 01 '23 at 11:46
  • so what i have done is,I have added lower(value) in my query and used the lowercase in pivot column names. – Narendra Jun 01 '23 at 11:51
  • I *assume* you mean `LOWER([Key])` @Narendra , as `value` inherets to collation of the database, and presumably your database isn't in a case sensitive collation (as otherwise this questions seem somewhat silly as *all* objects would case sensitive then). – Thom A Jun 01 '23 at 11:53
  • yeah i meant key – Narendra Jun 01 '23 at 11:56
  • you can also re-collate the key when returning i guess: ...select [key] collate SQL_Latin1_General_CP1_CI_AS as [key] ... – siggemannen Jun 01 '23 at 12:17