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.

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.

Value of the Type column JSON data type
0 null
1 string
2 int
3 true/false
4 array
5 object

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.

SELECT compatibility_level, Name FROM sys.databases SELECT compatibility_level, Name FROM sys.databases where Name='SWCDB'

OUTPUT: compatibility_level Name ------------------- ------- 110 SWCDB (1 row affected)

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

ALTER DATABASE SWCDB SET COMPATIBILITY_LEVEL = 130 SELECT compatibility_level, Name FROM sys.databases where Name='SWCDB'

OUTPUT: compatibility_level Name ------------------- ------- 130 SWCDB (1 row affected)

1) Example:

DECLARE @json NVARCHAR(MAX) SET @json=N'{"info":[{"color":"black", "category":"hue", "type":"primary", "code": {"rgba": [255,255,255,1], "hex": "#000"}}, {"color":"white", "category":"value", "code":{ "rgba":[0,0,0,1], "hex":"#FFF"}}]}' select * from OPENJSON(@json)


key value type
info [{"color":"black","category":"hue","type":"primary","code": {"rgba": [255,255,255,1],"hex": "#000"}},{"color":"white","category":"value","code":{"rgba":[0,0,0,1],"hex":"#FFF"}}] 4

2) Example:

DECLARE @json NVARCHAR(MAX) SET @json=N'{"info":[{"color":"black", "category":"hue", "type":"primary", "code": {"rgba": [255,255,255,1], "hex": "#000"}}, {"color":"white", "category":"value", "code":{ "rgba":[0,0,0,1], "hex":"#FFF"}}]}' select * from OPENJSON(@json,'$.info')


key value type
0 {"color":"black","category":"hue","type":"primary","code": {"rgba": [255,255,255,1],"hex": "#000"}} 5
1 {"color":"white","category":"value","code":{"rgba":[0,0,0,1],"hex":"#FFF"}} 5

DECLARE @json NVARCHAR(MAX) SET @json=N'{"info":[{"color":"black", "category":"hue", "type":"primary", "code": {"rgba": [255,255,255,1], "hex": "#000"}}, {"color":"white", "category":"value", "code":{ "rgba":[0,0,0,1], "hex":"#FFF"}}]}' select * from OPENJSON(@json,'$.info[0]')


key value type
color black 1
category hue 1
type primary 1
code {"rgba": [255,255,255,1],"hex":"#000"} 5

3) Example:

DECLARE @json NVARCHAR(MAX) SET @json=N'{"info":[{"color":"black", "category":"hue", "type":"primary", "code": {"rgba": [255,255,255,1], "hex": "#000"}}, {"color":"white", "category":"value", "code":{ "rgba":[0,0,0,1], "hex":"#FFF"}}]}' select * from OPENJSON(@json,'$.info[0].code')

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.


DECLARE @json NVARCHAR(MAX) = N'[ { "Order": { "Number":"SO43659", "Date":"2011-05-31T00:00:00" }, "AccountNumber":"AW29825", "Item": { "Price":2024.9940, "Quantity":1 } }, { "Order": { "Number":"SO43661", "Date":"2011-06-01T00:00:00" }, "AccountNumber":"AW73565", "Item": { "Price":2024.9940, "Quantity":3 } } ]' SELECT * FROM OPENJSON ( @json ) WITH ( Number varchar(200) '$.Order.Number', Date datetime '$.Order.Date', Customer varchar(200) '$.AccountNumber', Quantity int '$.Item.Quantity', [Order] nvarchar(MAX) AS JSON )


Number Date Customer Quantity Order
SO43659 2011-05-31 00:00:00.000 AW29825 1 {"Number":"SO43659","Date":"2011-05-31T00:00:00"}
SO43661 2011-06-01 00:00:00.000 AW73565 3 {"Number":"SO43661","Date":"2011-06-01T00:00:00"}

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)


DECLARE @pSearchOptions NVARCHAR(4000) = N'[990,992,993,994]' SELECT P.Name FROM Production.Product P INNER JOIN OPENJSON(@pSearchOptions) AS SelectedId ON P.ProductID = SelectedId.value

OUTPUT: Name ----------------------- Mountain-500 Black, 42 Mountain-500 Black, 48 Mountain-500 Black, 52 LL Bottom Bracket (4 rows affected)

Merge two JSON objects:

DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX) SET @json1=N'{"name": "Vishal", "surname":"Wish"}' SET @json2=N'{"name": "Pawan", "age":45}' SELECT * FROM OPENJSON(@json1) UNION ALL SELECT * FROM OPENJSON(@json2) WHERE [key] NOT IN (SELECT [key] FROM OPENJSON(@json1))

OUTPUT: key value type ------------ ------------ ---- name Vishal 1 surname Wish 1 age 45 2 (3 rows affected)


DECLARE @pSearchOptions NVARCHAR(4000) = N'[{"Id":990,"Status":"OK"},{"Id":992,"Status":"Tested"},{"Id":993,"Status":"Damage"},{"Id":994,"Status":"Faulty"}]' SELECT P.ProductID, P.Name,PS.Status FROM Production.Product P CROSS APPLY (SELECT * FROM OPENJSON(@pSearchOptions) with (ProductId int '$.Id',Status nvarchar(20) '$.Status') WHERE ProductID = P.ProductID) PS


ProductID Name Status
990 Mountain-500 Black, 42 OK
992 Mountain-500 Black, 48 Tested
993 Mountain-500 Black, 52 Damage
994 LL Bottom Bracket Faulty

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

If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.

Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.



Comment By: {{c.Author}}  On:   {{c.CreatedDate|date:'dd/MM/yyyy'}} / Reply