Read this post to explore about OPENJSON function in SQL Server. With our Server Support Services, Bobcares offers solutions to your queries.

OPENJSON Function In SQL Server

In SQL Server, the table-valued function OPENJSON aids in parsing JSON and also provides table-formatted data values and types for the JSON text. The columns in the rowset and the JSON property paths used to populate the columns can both be specified manually. OPENJSON can be used in the FROM clause of a Transact-SQL statement just like any other table, view, or table-valued function because it returns a set of rows.

For an app or service that can’t directly consume JSON, use OPENJSON to import JSON data into SQL Server or convert JSON data to relational representation. The following code gives the syntax:

OPENJSON( jsonExpression  [, jsonPath ] )[ WITH (column_mapping_definition1        [,column_mapping_definition2]         [,… column_mapping_definitionN])]

The OPENJSON function’s input parameter jsonExpression specifies the JSON text that will be parsed. Parameter jsonPath locate a specific JSON expression inside the JSON text and the OPENJSON() only parses this portion of the JSON text. The parameter that can be used to specifically specify the schema is the WITH clause. jsonPath and WITH are optional parameters.

An Example

The below OPENJSON function parses the JSON in SQL Server and returns the parsed JSON in the default schema format.

DECLARE @json NVarChar(2048) = N'{    "Owner": null,  "brand": "Trek",  "year": 2023,   "status": false,  "color": [ "red", "white", "yellow" ],    "Model": {    "name": "Trek SL 6",    "Fuel Type": "Petrol",    "TransmissionType": "Automatic",    "Turbo Charger": "true",    "Number of Cylinder": 4  }}';SELECT * FROM OpenJson(@json);

The OPENJSON() has produced three columns using the default schema in the result: The key column lists the key’s name, The value column displays the key’s value, and through the use of numbers, the type column displays the key column’s data types.

The JSON data type for Type columns is as follows:

• 0: null
• 1: string
• 2: int
• 3: true/false
• 4: array
• 5: object

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our Support team went over the details of using OPENJSON to parse JSON in SQL server details in this article. We also provide an example to explain it.