Openjson (transact-sql)

OPENJSON is a table-value function that parses JSON text and returns objects and properties in JSON as rows and columns. Data recovery lab OPENJSON provides a rowset view over a JSON document, with the ability to explicitly specify the columns in the rowset and the property paths to use to populate the columns. Data recovery online Since OPENJSON returns a set of rows, you can use OPENJSON function in FROM clause of Transact-SQL statements like any other table, view, or table-value function. Database union Note

The OPENJSON function is available only under compatibility level 130. Data recovery houston If your database compatibility level is lower than 130, SQL Server will not be able to find and execute OPENJSON function.


7m database soccer basketball Other JSON functions are available at all compatibility levels. Create database link You can check compatibility level in sys.databases view or in database properties. Database form You can change a compatibility level of database using the following command:

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.

You can call OPENJSON with or without an explicit schema that defines the structure of the output. Data recovery process For more info, see the Remarks and examples on this page.

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.

Is a JSON Path Expressions (SQL Server) that references an object or an array within the JSON expression. Database training OPENJSON will seek into JSON text at the specified position and parse only referenced fragment. Data recovery hardware If path is not provided, jsonExpression will be parsed.

For example, if you want to open an array of comments that is found under the feedback key in the JSON expression, you can specify the following path – ‘$.feedback.comments’ – to extract the array of comments from the JSON expression. Best database software See more examples later in this topic.

By default, OPENJSON returns elements or properties from the root object in the JSON expression. Database gui Provide a path if you want to open a JSON object nested within the JSON expression. Data recovery technician If you don’t specify a path, OPENJSON parses the root object using lax path mode (that is, as if you had specified the lax option in the path expression).

Some of the examples on this page explicitly specify the path mode, lax or strict. Database engine tuning advisor This is optional. Data recovery rates If you don’t explicitly specify a path mode, lax mode is the default. Database developer salary For more info about path mode and path expressions, 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).

By default, OPENJSON uses the name of the column to match a property in the JSON text. Database backup and recovery 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.

If the column name contains dots, OPENJSON uses it as the JSON path to the property that contains the values you want to extract. Yale b database For example, if you specify the column “info.name” in the schema, OPENJSON tries to populate this column with the property “name” from the parent object called “info” in the JSON text.

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

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

Use AS JSON option in column definition to specify that referenced property contains inner object or array. Database design 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. Database 3 normal forms If the path represents an object or an array, the function returns null in lax mode or an error in strict mode indicating that the property can’t be found at the specified path.

If you specify AS JSON for a column, the function returns a JSON fragment from the specified JSON property on the specified path. Database for dummies If the path represents a scalar value, the function returns null in lax mode or an error in strict mode indicating that the property can’t be found at the specified path. A database is a collection of This behavior is similar to the behavior of the JSON_QUERY function. Library database Note

If you want to return nested JSON fragment from some JSON property, you MUST specify AS JSON flag. Database node Without this option, OPENJSON will return NULL value instead of referenced JSON object or array, or it will return run-time error in strict mode (property cannot be found).

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.

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.

Key. Data recovery after format An nvarchar(4000) value that contains the name of the specified property or the index of the element in the specified array. Database 2015 The key column has a BIN2 collation.

Type. Data recovery nyc An int value that contains the type of the value. Database weekly The Type column is returned only when you use OPENJSON with the default schema. Data recovery utah The type column has one of the following values. Data recovery deleted files Value of the Type column

banner