CODEWITHSUNDEEP
sqlsql-serverjsonazure
Yong Jun Kim
Yong Jun Kim

Reputation: 382

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

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?

Upvotes: 6

Views: 12640

Answers (5)

Brock Nash
Brock Nash

Reputation: 1

I found this to be better than some of the other methods provided:

Coalesce(JSON_VALUE(@JsonData, 'lax $."propertyA"'),--Use expected case, result will be in proper case if successful
JSON_VALUE(lower(@JsonData), 'lax $."propertya"')) --NOTE: Use all lowercase for path - result will also be in lowercase

This works better because it allows the casing of the result to be correct as long as you have the initial casing of the path correct.

Upvotes: 0

mbazzano
mbazzano

Reputation: 21

Old question but I came across this when googling a similar issue so I will chip in with my solution:

SELECT @pb = PB from  
   OPENJSON(@PropertyBagsAsJson, '$."$values"') 
    WITH (                        
          PbId1 nvarchar(MAX) 'lax $.Id',
          PbId2 nvarchar(MAX) 'lax $.id',             
          PB nvarchar(MAX)  '$' AS JSON
    )
WHERE COALESCE(PbId1,PbId2) = @PropertyBagId

I hope that the example is clear. Basically I just add all possible casing of the property and then just use Coalesce to filter the results.

Upvotes: 2

Rainer Prem
Rainer Prem

Reputation: 59

You can use openjson. Instead of

    JSON_VALUE (SRQuestions.value, '$.id')

you can write

    (select Value 
       from openjson( SRQuestions.value ) 
      where [Key] collate latin1_general_ci = 'id')

You must use a Case-Insensitive "_ci" collation here. "UTF8_General_CI" works too, as does "database_default" if the database uses a CI collation.

Upvotes: 2

Dennis
Dennis

Reputation: 1848

Maybe you can just lower the json: 

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

Upvotes: 7

JESteph
JESteph

Reputation: 101

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.

Upvotes: 5

Related Questions