OPENJSON

OPENJSON#

Parses JSON text and returns its objects and properties as rows and columns. Use it in the FROM clause to turn a JSON string into a table, or with CROSS APPLY to open a JSON column of each row.

Syntax#

OPENJSON ( json_expression [ , path ] )
[ WITH ( column type [ column_path ] [ AS JSON ] [ , ...n ] ) ]

  • json_expression — the JSON text, often a variable or column. It must be an object or an array; a scalar document such as '5' is malformed text (error 13609).

  • path — an optional JSON path that selects the part of the document to open, $ by default. A lax path (the default) that finds nothing, or finds a scalar, returns no rows; a strict path raises error 13608 or 13611 instead. A NULL path raises error 8116.

  • WITH — an explicit output schema. Each column maps to a JSON value by column_path, or by its own name ($.name, matched case-sensitively); add AS JSON to return a nested object or array as JSON text. AS JSON requires an nvarchar(max) column.

A NULL json_expression returns no rows.

Return value#

Without a WITH clause, OPENJSON returns a row per property of the opened object, or per element of the opened array:

Column name

Data type

Description

key

nvarchar(4000) NOT NULL

The property name, or the zero-based index of the array element.

value

nvarchar(max)

The value as text: a string without its quotes, a number as written, true or false, an object or array as its JSON text, and NULL for a JSON null.

type

tinyint NOT NULL

0 null, 1 string, 2 number, 3 true or false, 4 array, 5 object.

With a WITH clause, an opened array returns a row per element and an opened object a single row, with the declared columns, each nullable. Each value is read as text and converted to the declared type the way CAST converts a string: a longer string is cut to the column length, and a value the type cannot hold fails the statement with the conversion error (for example 245). A column that finds nothing, or finds an object or array without AS JSON, is NULL; with a strict column path it raises error 13608 or 13624 instead.

Where the JSON is opened#

  • In a database backed by SQL Server, the source runs the function when the dialect version of its connection has it, and otherwise Querona parses the JSON itself. When the source cannot run it under APPLY with an argument from another table, the default federator renders the APPLY when its dialect version has the function; when it does not, or external federation is off, Querona parses the JSON once per driving row. It does so only when the call is the whole right side of APPLY: a query on the right side that reads the other table and calls OPENJSON - a derived table, or an inline function whose body calls it - is refused with a message that names the function. Set the dialect version of the connection to the version of the server to have the server run it.

  • Anywhere else, a SQL Server source or the default federator renders OPENJSON when the query reaches one whose dialect version has it, and otherwise Querona parses the JSON itself - once per driving row when the argument comes from another table under APPLY.

Which dialect version of a SQL Server connection has the function:

Dialect version

Without a path, or with a literal path

With a path from a variable, a parameter or a column

SQL Server (no version), SQL Server 2014

No

No

SQL Server 2016

Yes

No

SQL Server 2017 and later

Yes

Yes

Azure SQL Data Warehouse

Yes

Yes

The dialect version is part of the connection settings. Set it to the version of the server so that the server runs every call it can. The generic SQL Server version does not assume the server has OPENJSON, because a SQL Server 2014 or older server does not.

Note

SQL Server finds OPENJSON only in a database whose compatibility level is 130 or higher; at a lower level it answers error 208 “Invalid object name ‘OPENJSON’”, whatever its version. Querona does not read the compatibility level of the source database, so a source database below level 130 needs a dialect version that does not have the function, such as SQL Server 2014.

Note

When Querona parses the JSON itself, the position reported in a 13609 message comes from its own parser and can differ from the position SQL Server reports, and the $.sql:identity() column path is not supported. See Feature support.

Examples#

SELECT * FROM OPENJSON(@array) WITH (
    month     VARCHAR(3),
    temp      INT,
    month_id  TINYINT       '$.sql:identity()',
    data      NVARCHAR(MAX) AS JSON,
    data2     NVARCHAR(MAX) N'$.path' AS JSON
) AS months;

SELECT * FROM OPENJSON(@array, @path) AS months;

The properties of a JSON column of every row:

SELECT o.OrderID, j.[key], j.value
FROM dbo.Orders o
CROSS APPLY OPENJSON(o.Attributes) j
ORDER BY o.OrderID, j.[key];

See Also#