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.

[Is there a way to query JSON column in SQL Server ignoring capitalization of keys?](https://stackoverflow.com/questions/46915697/is-there-a-way-to-query-json-column-in-sql-server-ignoring-capitalization-of-key) — Lukasz Szozda, Aug 03 '20 at 17:48
I was looking for any other way, but no success yet. I decided to catch all those JSON properties separately and use union then. — Aryan Firouzian, Aug 03 '20 at 21:45

Zhorov · Accepted Answer · 2020-08-04T08:09:21.003

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

score -1 · Answer 2 · answered Nov 11 '22 at 14:05

-1

I know it's too late to give an answer, but just for the community the easiest way to figure this out is by applying LOWER or UPPER function to the json string. Something like this:

SET @Information = LOWER(@Information)

SELECT jsonInfo.*
FROM OPENJSON(@Information, N'$')
WITH(
    Comment NVARCHAR(MAX) N'$.comment',
    TimeStamp DATETIME'$.timestamp'
) AS jsonInfo;

answered Nov 11 '22 at 14:05

user8099525

31
4

This solution will also lowercase all of the values in the json. This may not be what you want to do! Here is a better answer: https://stackoverflow.com/a/63997172 – Jay Feb 24 '23 at 17:05

OPENJSON to ignore case when parsing JSON properties

2 Answers2