Deserialize json In Sql Server

Question

Hello , i want to deserialize json using sql server , I have find that Query. SELECT * FROM OpenJson(@json)    But when i  use this query in sql server then it's gives me  Invalid object name 'OpenJson'.   how can used it

Satya Karki · Accepted Answer

Could you check compatibility level on database? OPENJSON is available under compatibility level 130. Could you try to execute: ALTER   DATABASE  database_name  SET  COMPATIBILITY_LEVEL = 130   Also, if you are using JSON on Azure SQL Database, note that even new databases are created under 120 compatibility level so you should change it if you want to use OPENJSON.

Rijwan Ansari · Answer

Check this link. This might be because of campatibility.    https://database.guide/how-to-fix-invalid-object-name-openjson-in-sql-server/

Amira Bedhiafi · Answer

OPENJSON is a table-valued function that helps to parse JSON in SQL Server and it returns the data values and types of the JSON text in a table format. Now, we will look at the syntax of this function.
OPENJSON( jsonExpression  [, jsonPath ] )
[  WITH  (column_mapping_definition1
[,column_mapping_definition2]
[,… column_mapping_definitionN])
]   The jsonExpression is an input parameter that specifies the JSON text that will be parsed by the OPENJSON function.
The jsonPath is an optional parameter and it is used to seek a specified JSON expression in the JSON text and the OPENJSON function parses only this part of the JSON text.
The WITH clause is an optional parameter that can be used to explicitly specifies the schema.
DECLARE  @json NVarChar(2048) = N'{
"owner" :  null ,
"brand" :  "BMW" ,
"year" : 2020,
"status" :  false ,
"color" : [  "red" ,  "white" ,  "yellow"  ],
"Model" : {
"name" :  "BMW M4" ,
"Fuel Type" :  "Petrol" ,
"TransmissionType" :  "Automatic" ,
"Turbo Charger" :  "true" ,
"Number of Cylinder" : 4
}
}';
SELECT  *  FROM  OpenJson(@json);
The key column indicates the name of the key
The value column shows the value of the key
The type column indicates the data types of the key column through the numbers. The following table illustrates the possible values of the type column and their data type explanations

Sachin Singh · Answer

change the compatiblity level and then test again
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 130;   SELECT * FROM OpenJson(@json)

Ajay Kumar · Answer

My Db compatiblity level is 100, Can i change this compatiblity level, And If Yes There is any affect in Database, Acutally My DB is on Live ,