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 · 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.

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)

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/

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 ,