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.

Syntax

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

• json_expression — the JSON text, often a variable or column.

• path — an optional JSON path that selects the part of the document to expose.

• WITH — an explicit output schema. Each column maps to a JSON value by column_path (or by name); add AS JSON to return a nested object or array as JSON text.

Without a WITH clause, OPENJSON returns the default key, value and type columns.

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;

See Also

• OPENQUERY

• OPENROWSET

• JSON