SQL Server OPENJSON() Function

Summary: in this tutorial, you will learn how to use the SQL Server OPENJSON() function to parse JSON text and convert its elements into rows and columns.

Introduction to the SQL Server OPENJSON() function

The OPENJSON() function is a table-valued function that parses a JSON string and returns values from the input JSON as rows and columns. In other words, the OPENJSON() function converts a JSON string into rows and columns.

Here’s the basic syntax of the OPENJSON() function:

OPENJSON(json_expression [, path])
  [WITH (
    option1 [,...optionN]
  )]Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_expression: This is the JSON document that you want to parse.
• path: This JSON path specifies the JSON element to extract. The path is optional. If you omit it, the OPENJSON() function will parse the entire JSON document.
• option1, option2, …: These are optional parameters that specify additional options such as data type conversion, schema definition, and error handling.

SQL Server OPENJSON() function examples

Let’s take some examples of using the OPENJSON() function.

1) Basic OPENJSON() function example

Suppose you have the following JSON document:

{
  "name": "John",
  "age": 25,
  "skills": [
    "SQL Server",
    "C#",
    ".NET"
  ],
  "address": null,
  "active": true,
  "phone": {
    "work": "(408)-123-4567",
    "home": "(408)-789-1234"
  }
}Code language: JSON / JSON with Comments (json)

The following example uses the OPENJSON() function to parse and convert the JSON document into rows and columns:

SELECT
  * 
FROM
  OPENJSON(
    '{"name": "John", "age": 25, "skills" : ["SQL Server","C#", ".NET"], "address":null, "active": true, "phone": {"work": "(408)-123-4567", "home": "(408)-789-1234"}}'
  ) 
ORDER BY
  type;Code language: SQL (Structured Query Language) (sql)

Output:

key     | value                                                | type
-----------------------------------------------------------------------
address | None                                                 | 0
name    | John                                                 | 1
age     | 25                                                   | 2
active  | true                                                 | 3
skills  | ["SQL Server","C#", ".NET"]                          | 4
phone   | {"work": "(408)-123-4567", "home": "(408)-789-1234"} | 5Code language: SQL (Structured Query Language) (sql)

The output has three columns key, value, and type.

The keys and values in the result set correspond to the keys and values in the JSON document. The value of the type column determines the data type of the corresponding value in the result:

2) Using the OPENJSON() function with an explicit structure

The WITH clause in the OPENJSON() function allows you to specify a schema for the result set. The OPENJSON() function will return a result set with only the columns you define in the WITH clause.

The following example uses the OPENJSON() function to parse and convert elements of a JSON string to a specific schema:

SELECT
  * 
FROM
  OPENJSON(
    '{"name": "John", "age": 25, "skills" : ["SQL Server","C#", ".NET"], "address":null, "active": true, "phone": {"work": "(408)-123-4567", "home": "(408)-789-1234"}}'
  ) 
WITH (
	name VARCHAR(100)  '$.name',
	age INT  '$.age',
	active BIT '$.active',
	work_phone VARCHAR(20) '$.phone.work',
	home_phone VARCHAR(25) '$.phone.home',
	address VARCHAR(255) '$.address'
);Code language: SQL (Structured Query Language) (sql)

Output:

name | age | active | work_phone     | home_phone     | address
----------------------------------------------------------------
John | 25  | True   | (408)-123-4567 | (408)-789-1234 | NULLCode language: SQL (Structured Query Language) (sql)

Summary

• Use the OPENJSON() function to parse JSON text and convert its elements into rows and columns.