OPENJSON function in Sql

In previous article, we were learn about how we can get property's value from JSON string using ISJSON, JSON_VALUE, JSON_QUERY and JSON_MODIFY functions in Sql

Today we are going to learn one more powerful JSON function as follows

OPENJSON: OPENJSON is a table-valued function, Used to convert JSON object string into table with columns.

Syntax:
OPENJSON( jsonExpression [ , path ] ) [ ]
::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

jsonExpression:  jsonExpression, is a Unicode character expression containing JSON text.

path:  Path, is an optional JSON path expression that references an object or an array within jsonExpression.

with_clause:  with_clause, explicitly defines the output schema for the OPENJSON function to return.

colName:  colName is the name for the output column.

type:  type is the data type for the output column.

column_path: column_path is the JSON path that specifies the property to return in the specified column. also used to override default mapping rules when the name of an output column doesn't match the name of the property.

AS JSON:  Use the AS JSON option in a column definition to specify that the referenced property contains an inner JSON object or array.

Note:  The type of the column must be NVARCHAR(MAX) for AS JSON.

Return value

Key:  key, is an nvarchar(4000) value that contains the name of the specified property or the index of the element in the specified array.

Value:  Value, is an nvarchar(max) value that contains the value of the property.

Type:  Type, is an int value that contains the type of the value.

Note:  OPENJSON function is available only under compatibility level 130 or higher. If your database compatibility level is lower than 130, SQL Server can't find and run the OPENJSON function.

We can check compatibility level by the following query.

OUTPUT:
compatibility_level Name
------------------- -------
110                 SWCDB

(1 row affected)

We can change the compatibility level of a database with the following query.

OUTPUT:
compatibility_level Name
------------------- -------
130                 SWCDB

(1 row affected)

1) Example:

OUTPUT:

2) Example:

OUTPUT:

OUTPUT:

3) Example:

OUTPUT:
key       value                type
--------- -------------------- ----
rgba      [255,255,255,1]      4
hex       #000                 1

(2 rows affected) 

with_clause: with_clause defines the output schema for the OPENJSON function to return.

Example:

OUTPUT:

Convert a JSON array to a temporary table:

DECLARE @pSearchOptions NVARCHAR(4000) = N'[110,12,13,14]' ;with cte as( SELECT * FROM OPENJSON(@pSearchOptions) AS SelectedId) SELECT CTE.value FROM CTE

OUTPUT:
  value
------------
110
12
13
14

(4 rows affected)

INNER JOIN:

OUTPUT:
Name
-----------------------
Mountain-500 Black, 42
Mountain-500 Black, 48
Mountain-500 Black, 52
LL Bottom Bracket

(4 rows affected)

Merge two JSON objects:

OUTPUT:

key          value        type
------------ ------------ ----
name         Vishal       1
surname      Wish         1
age          45           2

(3 rows affected)

CROSS APPLY:

OUTPUT:

(4 rows affected)

In such a way we can apply all table function queries with JSON.

To learn, How we can convert C# Object in the form of JSON object and store JSONG object stream into SQL Server Database click here.