OPENJSON (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns.

OPENJSON provides a rowset view over a JSON document. You can explicitly specify the columns in the rowset and the property paths to use to populate the columns. Since OPENJSON returns a set of rows, you can use OPENJSON in the FROM clause of Transact-SQL statements like any other table, view, or table-valued function.

Use OPENJSON to import JSON data into SQL Server, or to convert JSON data to relational format for an app or service that can't consume JSON directly.

Note

The 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. Other JSON functions are available at all compatibility levels. You can check compatibility level in the sys.databases view or in database properties. You can change the compatibility level of a database with the following command:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Compatibility level 120 might be the default even in a new Azure SQL Database.

Topic link iconTransact-SQL Syntax Conventions

Syntax

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )
Note

If you use the AS JSON option, the column type must be NVARCHAR(MAX).

The OPENJSON table-valued function parses the jsonExpression provided as the first argument and returns one or more rows containing data from the JSON objects in this expression. jsonExpression can contain nested sub-objects. If you want to parse a sub-object from within jsonExpression, you can specify a second path parameter for the JSON sub-object.

openjson

Syntax for OPENJSON TVF

By default, the OPENJSON table-valued function returns three columns, which contain the key name, value, and type of each {key:value} pair found in jsonExpression. As an alternative, you can explicitly specify the schema of the result set OPENJSON returns by using with_clause:

with_clause

Syntax for WITH clause in OPENJSON TVF

with_clause contains a list of columns with their types for OPENJSON to return. By default, OPENJSON matches keys in jsonExpression with the column names in with_clause. If a column name does not match a key name, you can provide an optional column_path, which is a JSON Path Expression that references a key within the jsonExpression.

Arguments

jsonExpression

Is a Unicode character expression containing the JSON text.

OPENJSON iterates over the elements of the array or the properties of the object in the JSON expression and returns one row for each element or property. The following example returns each property of the object provided as jsonExpression:

DECLARE @json NVARCHAR(4000) = N'{  
   "StringValue":"John",  
   "IntValue":45,  
   "TrueValue":true,  
   "FalseValue":false,  
   "NullValue":null,  
   "ArrayValue":["a","r","r","a","y"],  
   "ObjectValue":{"obj":"ect"}  
}'

SELECT *
FROM OPENJSON(@json)

Results

key value type
StringValue John 1
IntValue Doe 2
TrueValue true 3
FalseValue false 3
NullValue NULL 0
ArrayValue ["a","r","r","a","y"] 4
ObjectValue {"obj":"ect"} 5

path

Is a JSON path expression that references an object or an array within jsonExpression. OPENJSON seeks into the JSON text at the specified position and parses only the referenced fragment. For more info, see JSON Path Expressions (SQL Server).

The following example returns a nested object by specifying the path:

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key],value
FROM OPENJSON(@json,'$.path.to."sub-object"')

Results

Key Value
0 en-GB
1 en-UK
2 de-AT
3 es-AR
4 sr-Cyrl

OPENJSON returns the indexes of the elements in the JSON text as keys when it parses a JSON array.

In SQL Server 2017 and in Azure SQL Database, you can provide a variable as the value of path.

The comparison used to match path steps with the properties of the JSON expression is case-sensitive and collation-unaware (that is, a BIN2 comparison).

with_clause

Explicitly defines the output schema for the OPENJSON function to return. The with_clause can contain the following elements:

colName Is the name for the output column.

By default, OPENJSON uses the name of the column to match a property in the JSON text. For example, if you specify the column name in the schema, OPENJSON tries to populate this column with the property "name" in the JSON text. You can override this default mapping by using the column_path argument.

type
Is the data type for the output column.

column_path
Is the JSON path that specifies the property to return in the specified column. For more info, see the description of the path parameter previously in this topic.

Use column_path to override default mapping rules if the name of an output column doesn't match the name of the property.

For more info, see JSON Path Expressions (SQL Server).

The comparison used to match path steps with the properties of the JSON expression is case-sensitive and collation-unaware (that is, a BIN2 comparison).

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

  • If you don't specify AS JSON for a column, the function returns a scalar value (for example, int, string, true, false) from the specified JSON property on the specified path. If the path represents an object or an array, and the property can't be found at the specified path, the function returns null in lax mode or returns an error in strict mode. This behavior is similar to the behavior of the JSON_VALUE function.

  • If you specify AS JSON for a column, the function returns a JSON fragment from the specified JSON property on the specified path. If the path represents a scalar value, and the property can't be found at the specified path, the function returns null in lax mode or returns an error in strict mode. This behavior is similar to the behavior of the JSON_QUERY function.

Note

If you want to return a nested JSON fragment from a JSON property, you have to provide the AS JSON flag. Without this option, if the property can't be found, OPENJSON returns a NULL value instead of the referenced JSON object or array, or it returns a run-time error in strict mode .

If you specify the AS JSON option, the type of the column must be NVARCHAR(MAX).

For example, the following query returns and formats the elements of an array:

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  
 )

Results

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

Return Value

The columns that the OPENJSON function returns depend on the WITH option.

  1. When you call OPENJSON with the default schema - that is, when you don't specify an explicit schema in the WITH clause - the function returns a table with the following columns:

    1. Key. An nvarchar(4000) value that contains the name of the specified property or the index of the element in the specified array. The key column has a BIN2 collation.
    2. Value. An nvarchar(max) value that contains the value of the property. The value column inherits its collation from jsonExpression.
    3. Type. An int value that contains the type of the value. The Type column is returned only when you use OPENJSON with the default schema. The type column has one of the following values:

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

      Only first-level properties are returned. The statement fails if the JSON text is not properly formatted.

  2. When you call OPENJSON and you specify an explicit schema in the WITH clause, the function returns a table with the schema that you defined in the WITH clause.

Remarks

json_path used in the second argument of OPENJSON or in with_clause can start with the lax or strict keyword.

  • In lax mode, OPENJSON doesn't raise an error if the object or value on the specified path can't be found. If the path can't be found, OPENJSON returns either an empty result set or a NULL value.
  • In strict, mode OPENJSON returns an error if the path can't be found.

Some of the examples on this page explicitly specify the path mode, lax or strict. The path mode is optional. If you don't explicitly specify a path mode, lax mode is the default. For more info about path mode and path expressions, see JSON Path Expressions (SQL Server).

Column names in with_clause are matched with keys in the JSON text. If you specify the column name [Address.Country], it's matched with the key Address.Country. If you want to reference a nested key Country within the object Address, you have to specify the path $.Address.Country in column path.

json_path may contain keys with alphanumeric characters. Escape the key name in json_path with double quotes if you have special characters in the keys. For example, $."my key $1".regularKey."key with . dot" matches value 1 in the following JSON text:

{
  "my key $1": {
    "regularKey":{
      "key with . dot": 1
    }
  }
}

Examples

Example 1 - Convert a JSON array value to a temporary table

The following example provides a list of identifiers as a JSON array of numbers. The query converts the JSON array to a table of identifiers and filters all products with the specified ids.

DECLARE @pSearchOptions NVARCHAR(4000) =N'[1,2,3,4]'

SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
 ON product.productTypeID=productTypes.value

This query is equivalent to the following example. However, in the example below, you have to embed numbers in the query instead of passing them as parameters.

SELECT *
FROM products
WHERE product.productTypeID IN(1,2,3,4)

Example 2 - Merge properties from two JSON objects

The following example selects a union of all the properties of two JSON objects. The two objects have a duplicate name property. The example uses the key value to exclude the duplicate row from the results.

DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX)

SET @json1=N'{"name": "John", "surname":"Doe"}'

SET @json2=N'{"name": "John", "age":45}'

SELECT *
FROM OPENJSON(@json1)
UNION ALL
SELECT *
FROM OPENJSON(@json2)
WHERE [key] NOT IN(SELECT [key] FROM OPENJSON(@json1))

Example 3 - Join rows with JSON data stored in table cells using CROSS APPLY

In the following example, the SalesOrderHeader table has a SalesReason text column that contains an array of SalesOrderReasons in JSON format. The SalesOrderReasons objects contain properties like Quality and Manufacturer. The example creates a report that joins every sales order row to the related sales reasons. The OPENJSON operator expands the JSON array of sales reasons as if the reasons were stored in a separate child table. Then the CROSS APPLY operator joins each sales order row to the rows returned by the OPENJSON table-valued function.

SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)
Tip

When you have to expand JSON arrays stored in individual fields and join them with their parent rows, you typically use the Transact-SQL CROSS APPLY operator. For more info about CROSS APPLY, see FROM (Transact-SQL).

The same query can be rewritten by using OPENJSON with an explicitly defined schema of rows to return:

SELECT SalesOrderID, OrderDate, value AS Reason  
FROM Sales.SalesOrderHeader  
     CROSS APPLY OPENJSON (SalesReasons) WITH (value nvarchar(100) '$')

In this example, the $ path references each element in arrays. If you want to explicitly cast the returned value, you can use this type of query.

Example 4 - Combine relational rows and JSON elements with CROSS APPLY

The following query combines relational rows and JSON elements into the results shown in the following table.

SELECT store.title, location.street, location.lat, location.long  
FROM store  
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')   
     WITH (street varchar(500) ,  postcode  varchar(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     AS location

Results

title street postcode lon lat
Whole Food Markets 17991 Redmond Way WA 98052 47.666124 -122.10155
Sears 148th Ave NE WA 98052 47.63024 -122.141246,17

Example 5 - Import JSON data into SQL Server

The following example loads an entire JSON object into a SQL Server table.

DECLARE @json NVARCHAR(max)  = N'{  
  "id" : 2,  
  "firstName": "John",  
  "lastName": "Smith",  
  "isAlive": true,  
  "age": 25,  
  "dateOfBirth": "2015-03-25T12:00:00",  
  "spouse": null  
  }';  

  INSERT INTO Person  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id int,  
        firstName nvarchar(50), lastName nvarchar(50),   
        isAlive bit, age int,  
        dateOfBirth datetime2, spouse nvarchar(50)

See Also

JSON Path Expressions (SQL Server)
Convert JSON Data to Rows and Columns with OPENJSON (SQL Server)
Use OPENJSON with the Default Schema (SQL Server)
Use OPENJSON with an Explicit Schema (SQL Server)