Is there a way to query JSON column in SQL Server ignoring capitalization of keys?

Question

I am trying to query a JSON column that has mixed capitalization. For instance, some rows have keys that are all lower case like below:

{"name":"Screening 1","type":"template","pages":[{"pageNumber":1,...}

However, some of the rows have keys that are capitalized on its first letter like this:

{"Type":"template","Name":"Screening2","Pages":[{"PageNumber":1,...}

Unfortunately, SQL Server seems to only supports JSON path system that is case sensitive. Therefore, I can't query on all rows successfully. If I use lower case path like '$.pages' in a query like below:

SELECT ST.Id AS Screening_Tool_Id
    , ST.Name AS Screening_Tool_Name
    , ST.Description AS Screening_Tool_Description
    , COUNT(JSON_VALUE (SRQuestions.value, '$.id'))  AS Question_Specific_Id

FROM dbo.ScreeningTemplate AS ST
    CROSS APPLY OPENJSON(ST.Workflow, '$.pages') AS SRPages
        CROSS APPLY OPENJSON(SRPages.Value, '$.sections') AS SRSections

I miss any row that has capitalized keys. Is there any way to query all rows ignoring their capitalization?

Answer 1

According to MS, looks like you're stuck with a case-sensitive query:

When OPENJSON parses a JSON array, the function returns the indexes of the elements in the JSON text as keys.+ The comparison used to match path steps with the properties of the JSON expression is case-sensitive and collation-unaware (that is, a BIN2 comparison). https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql

If the only variations are in the capitalization of the first character, you could try to work around this limitation by creating queries with the variants and UNION the results together.

Answer 2

Maybe you can just lower the json:

COUNT(JSON_VALUE (lower(SRQuestions.value), '$.id'))  AS Question_Specific_Id