OPENJSON to ignore case when parsing JSON properties

Question

Lets say there is a table A that has column Information, and data is stored there in JSON format. JSON string, stored there, may have properties Comment and Timestamp or properties comment and timestamp. Like this:

[{"Timestamp":"2018-04-11 18:14:59.9708","Comment":"first comment"}]
[{"timestamp":"2017-04-11 18:14:59.9708","comment":"second comment"}]
[{"Timestamp":"2019-04-11 18:14:59.9708","Comment":"third comment"}, {"timestamp":"2017-04-11 18:14:59.9708","comment":"last comment"}]

Below script parses the JSON string only for capital case properties, and throw error for JSON string with small cases.

Select jsonInfo.*
From OPENJSON(@Information, N'$')
    with(
        Comment nvarchar(max) N'$.Comment',
        TimeStamp datetime '$.Timestamp'
    ) as jsonInfo;

Is there any syntax that return both Comment or comment properties, by ignoring case.

Answer 1

As is explained in the documentation, with explicit schema (the WITH clause), OPENJSON() matches keys in the input JSON expression with the column names in the WITH clause and the match is case sensitive. But, as a possible workaround, you may try to use OPENJSON() with default schema and conditional aggregation:

Statement:

DECLARE @information nvarchar(max) = N'[
   {"Timestamp":"2019-04-11 18:14:59.9708","Comment":"third comment"}, 
   {"timestamp":"2017-04-11 18:14:59.9708","comment":"last comment"}
]'

SELECT 
   MAX(CASE WHEN LOWER(j2.[key]) = N'timestamp' THEN j2.[value] END) AS [TimeStamp],
   MAX(CASE WHEN LOWER(j2.[key]) = N'comment' THEN j2.[value] END) AS [Comment]
FROM OPENJSON(@information, '$') j1
CROSS APPLY OPENJSON(j1.[value]) j2
GROUP BY j1.[key]

Result:

TimeStamp                   Comment
-----------------------------------------
2019-04-11 18:14:59.9708    third comment
2017-04-11 18:14:59.9708    last comment