Summary: in this tutorial, you will learn how to use the SQL Server FOR JSON clause to format query results as JSON text.
Introduction to the SQL Server FOR JSON clause
The FOR JSON clause allows you to format a query result as JSON text.
The FOR JSON clause can be useful when you want to retrieve data from SQL Server and serialize it as JSON for consumption by applications or web services.
Here’s the basic syntax of the FOR JSON clause:
SELECT select_list
FROM table_name
FOR JSON AUTO | PATH, extra_option;Code language: SQL (Structured Query Language) (sql)In this syntax:
- First, specify the
FOR JSONclause at the end of a query. - Second, provide the JSON output format in the JSON format. The
FOR JSON AUTOformats JSON automatically based on the query set whereas theFOR JSON PATHallows you to have full control over the format of the JSON output.
The following extra options allow you to control the output of the JSON document:
ROOT: Add a single, top-level element to the JSON output. If you skip this, the JSON output won’t have a root element.INCLUDE_NULL_VALUES: The function doesn’t include NULL in the output JSON. If you want to convert NULL to null values and include them in the output JSON, you can use theINCLUDE_NULL_VALUESoption.WITHOUT_ARRAY_WRAPPER: Use this option to format a single-row result as an object. Otherwise, the function will place the JSON object into a JSON array.
The FOR JSON is a counterpart of the OPENJSON() function, which converts a JSON document to rows and columns.
SQL Server FOR JSON clause examples
Let’s take some examples of using the FOR JSON clause.
We’ll use the products and categories tables from the sample database:
1) Basic SQL Server FOR JSON clause example
The following example retrieves the top 5 most expensive products and formats the result set as JSON using the FOR JSON clause:
SELECT
TOP 5 product_id,
product_name,
list_price
FROM
production.products
ORDER BY
list_price DESC
FOR JSON AUTO;Code language: SQL (Structured Query Language) (sql)Output:
[
{
"product_id": 155,
"product_name": "Trek Domane SLR 9 Disc - 2018",
"list_price": 11999.99
},
{
"product_id": 149,
"product_name": "Trek Domane SLR 8 Disc - 2018",
"list_price": 7499.99
},
{
"product_id": 51,
"product_name": "Trek Silque SLR 8 Women's - 2017",
"list_price": 6499.99
},
{
"product_id": 156,
"product_name": "Trek Domane SL Frameset - 2018",
"list_price": 6499.99
},
{
"product_id": 157,
"product_name": "Trek Domane SL Frameset Women's - 2018",
"list_price": 6499.99
}
]Code language: SQL (Structured Query Language) (sql)2) Using the FOR JSON clause with a specified root document
The following example retrieves the top 5 most expensive products and formats the result set with the root document as products:
SELECT
TOP 5 product_id,
product_name,
list_price
FROM
production.products
ORDER BY
list_price DESC
FOR JSON AUTO, ROOT('products');Code language: SQL (Structured Query Language) (sql)Output:
{
"products": [
{
"product_id": 155,
"product_name": "Trek Domane SLR 9 Disc - 2018",
"list_price": 11999.99
},
{
"product_id": 149,
"product_name": "Trek Domane SLR 8 Disc - 2018",
"list_price": 7499.99
},
{
"product_id": 51,
"product_name": "Trek Silque SLR 8 Women's - 2017",
"list_price": 6499.99
},
{
"product_id": 156,
"product_name": "Trek Domane SL Frameset - 2018",
"list_price": 6499.99
},
{
"product_id": 157,
"product_name": "Trek Domane SL Frameset Women's - 2018",
"list_price": 6499.99
}
]
}Code language: SQL (Structured Query Language) (sql)3) Using the FOR JSON clause with aggregate functions
The following example retrieves the category and product count in each category, returning the result set as JSON text:
SELECT
c.category_name,
COUNT(*) product_count
FROM
production.categories c
INNER JOIN production.products p on c.category_id = p.category_id
GROUP BY
c.category_name
ORDER BY
product_count FOR JSON AUTO;Code language: SQL (Structured Query Language) (sql)Output:
[
{
"category_name": "Cyclocross Bicycles",
"product_count": 10
},
{
"category_name": "Electric Bikes",
"product_count": 24
},
{
"category_name": "Comfort Bicycles",
"product_count": 30
},
{
"category_name": "Children Bicycles",
"product_count": 59
},
{
"category_name": "Mountain Bikes",
"product_count": 60
},
{
"category_name": "Road Bikes",
"product_count": 60
},
{
"category_name": "Cruisers Bicycles",
"product_count": 78
}
]Code language: SQL (Structured Query Language) (sql)4) Using the FOR JSON clause with a single-row result
The following example retrieves a product with id 1 from the production.products table and format the result set as JSON using the FOR JSON AUTO clause:
SELECT
product_id,
product_name,
list_price
FROM
production.products
WHERE
product_id = 1
FOR JSON AUTO;Code language: JavaScript (javascript)Output:
[
{
"product_id": 1,
"product_name": "Trek 820 - 2016",
"list_price": 379.99
}
]Code language: JSON / JSON with Comments (json)The output JSON is an array that consists of one JSON object.
If you use the WITHOUT_ARRAY_WRAPPER option, the FOR JSON clause will return a single JSON object instead. For example:
SELECT
product_id,
product_name,
list_price
FROM
production.products
WHERE
product_id = 1
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;Code language: JavaScript (javascript)Output:
{
"product_id": 1,
"product_name": "Trek 820 - 2016",
"list_price": 379.99
}Code language: JSON / JSON with Comments (json)5) Using FOR JSON clause to create nested objects
The following example uses the FOR JSON clause to create JSON that includes orders and order items of the customer id 1:
SELECT
o.order_id,
o.order_status,
o.order_date,
(
SELECT
oi.item_id,
oi.product_id,
oi.quantity,
oi.list_price,
oi.discount
FROM
sales.order_items oi
WHERE
oi.order_id = o.order_id
FOR JSON PATH
) AS items
FROM
sales.orders o
WHERE
o.customer_id = 1
FOR JSON PATH;Code language: SQL (Structured Query Language) (sql)Output:
[
{
"order_id": 599,
"order_status": 4,
"order_date": "2016-12-09",
"items": [
{
"item_id": 1,
"product_id": 9,
"quantity": 2,
"list_price": 2999.99,
"discount": 0.05
},
{
"item_id": 2,
"product_id": 22,
"quantity": 2,
"list_price": 269.99,
"discount": 0.2
},
{
"item_id": 3,
"product_id": 23,
"quantity": 1,
"list_price": 299.99,
"discount": 0.07
},
{
"item_id": 4,
"product_id": 10,
"quantity": 2,
"list_price": 1549,
"discount": 0.1
}
]
},
{
"order_id": 1555,
"order_status": 1,
"order_date": "2018-04-18",
"items": [
{
"item_id": 1,
"product_id": 24,
"quantity": 2,
"list_price": 549.99,
"discount": 0.1
},
{
"item_id": 2,
"product_id": 156,
"quantity": 1,
"list_price": 6499.99,
"discount": 0.1
},
{
"item_id": 3,
"product_id": 126,
"quantity": 1,
"list_price": 469.99,
"discount": 0.1
},
{
"item_id": 4,
"product_id": 128,
"quantity": 2,
"list_price": 1899,
"discount": 0.05
},
{
"item_id": 5,
"product_id": 174,
"quantity": 1,
"list_price": 3199.99,
"discount": 0.2
}
]
},
{
"order_id": 1613,
"order_status": 3,
"order_date": "2018-11-18",
"items": [
{
"item_id": 1,
"product_id": 153,
"quantity": 1,
"list_price": 4999.99,
"discount": 0.07
},
{
"item_id": 2,
"product_id": 283,
"quantity": 2,
"list_price": 319.99,
"discount": 0.05
}
]
}
]Code language: JSON / JSON with Comments (json)Summary
- Use the
FOR JSONclause to format a query’s result into a JSON document.
