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

sql server json remove escape characters
sql server json array to rows
sql server json array length
sql server json without column name
cross apply openjson
json to sql server
sql server json value max length
openjson is not a recognized built-in function name

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?

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://docs.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.

SQL SERVER - Understanding JSON Use is Case-Sensitive, Whenever I get a new toy for some occasion, I see there is at least a half hour of exploration to understand how the toy works and she goes into  You don't need a custom query language to query JSON in SQL Server. To query JSON data, you can use standard T-SQL. If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).

Maybe you can just lower the json:

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

JSON is Case Sensitive. You Don't Have to Be., SQL, by default, is case insensitive to identifiers and keywords, but case sensitive to data. JSON is case sensitive to both field names and data. 3 Is there a way to query JSON column in SQL Server ignoring capitalization of keys? Oct 24 '17 3 Use a parameter or variable to set connection timeout in SSIS?

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.

Solve common issues with JSON in SQL Server, I want to create a JSON text result from a simple SQL query on a single table. How can I create a custom JSON format that the existing options Every result of a FOR JSON query or the JSON_QUERY function in the column When I reference a key in the WITH clause, only scalar values are returned. Since there is no native JSON format on SQL Server, the data type of this column is just a nvarchar(max) Here’s how a simple SELECT looks like: Now we want to query our customers table by a customer service id (which is inside that json).

How to do a case-insensitive LIKE in a case-sensitive database , sql-server collation like The way you are writing the LIKE piece also makes the query collation information though from Ben Snaidero from MS SQL Tips. or LOWER and you can re-collate the column so that it's no longer case TABLE #​tmp_cohellation_fun ( ID INT IDENTITY(1,1) PRIMARY KEY  Computed Columns in SQL Server. The only way to get JSON indexes working on SQL server is to use a computed column. A computed column is basically a column that performs a function to calculate its values. For example, let’s say we have a table with some car JSON data in it:

The JSON_QUERY() function to extract objects from JSON Data, In the below example, we have a key (Name), and its value is a JSON object (​nested JSON) SQL Server provides the following JSON functions to work with JSON Data: mode: It controls the output of a JSON_QUERY() function in case of At this step, we can use the JSON function to query data in the  Starting SQL Server 2016 Microsoft deployed a set of functions that allow us to work with JSON data in a structured way inside SQL Server. I will introduce a small usage sample for the JSON_VALUE function in combination with indexes to improve information retrieval from a table containing one JSON object.

Friday the 13th - JSON is coming to SQL Server, JSON support in SQL Server is one of the highly ranked feature requests on SELECT [key] as property, value FROM OPENJSON(@json, In the following sections we will see how you can query JSON text Equality predicate will ignore case sensitivity if JSON column AdditionalInfo is case insensitive. This works (in SQL Server 2017, where STRING_AGG is available), but is quite clumsy. I'm not sure there's not a more elegant way. SELECT ( SELECT ID, ValueV, Keys = JSON_QUERY('["' + STRING_AGG(STRING_ESCAPE(Keys, 'json'), '","') + '"]') FOR JSON PATH ) FROM #Test GROUP BY ID, ValueV

Comments
  • I was thinking about using UNION as well, and it seems like the best way at the moment. It will be better than re-writing all the JSON data.
  • This is a good solution if you do not have case sensitive data. In my case, values in the JSON were numbers, so it was fine to use.