I have used "JSON_VALUE" function in SQL Query.It works fine as per my requirement. I would like to know how to skip case sensitive in json field.Please check my example jsonData

Json Data: -

{
  "factoryID": "4",
  "yearID": "2",
  "loggedInUserID": "43",
  "pageIndex": "1",
  "pageSize": "10",
  "searchString": "",
  "whereString": ""
}

but when I give field name in Camel case in Json_Value ,then its return null.Please advise how to skip case sensitive in "Json_Value"

SQL:-

 set @FactoryID = JSON_VALUE(@JsonData,'$.FactoryID')
set @YearID = JSON_VALUE(@JsonData,'$.YearID')

For Json Detail (Array) :-

    SELECT          
    JSON_VALUE(A.value,'$.FactoryID')FactoryID,
    JSON_VALUE(A.value,'$.YearID')YearID,
    JSON_VALUE(A.value,'$.CreatedUserID')CreatedUserID,
    JSON_VALUE(A.value,'$.ModifiedUserID')ModifiedUserID,
    JSON_VALUE(A.value,'$.TempID')TempID

FROM OPENJSON(@JsonDetail) as A

CodePudding user response：

I'm afraid there's no built-in way to do this.
But there are a few options available:

1. You can use OPENJSON that returns a set of (key, value, type) and work directly with the key/value pairs (though this approach won't work in case the value you need is an object):

   DECLARE @json VARCHAR(500) = '{
     "factoryID": "4",
     "yearID": "2",
     "loggedInUserID": "43",
     "pageIndex": "1",
     "pageSize": "10",
     "searchString": "",
     "whereString": ""
   }';
   
   DECLARE @FactoryID INT,
           @YearID INT;
   
   SELECT @YearID = IIF(LOWER([key]) = 'yearid', [value], @YearID),
          @FactoryID = IIF(LOWER([key]) = 'factoryid', [value], @FactoryID)
   FROM OPENJSON(@json);
   
   SELECT @YearID AS YearID,
          @FactoryID AS FactoryID;
   

2. Another option is to create a custom CLR function to parse JSON in a case-insensitive way.