Aryan Firouzian
Aryan Firouzian

Reputation: 2016

OPENJSON to ignore case when parsing JSON properties

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.

Upvotes: 1

Views: 2312

Answers (2)

user8099525
user8099525

Reputation: 41

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;

Upvotes: -1

Zhorov
Zhorov

Reputation: 29983

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

Upvotes: 2

Related Questions