Skip to content

use-openjson-with-the-default-schema-sql-server.md

Latest commit

 

History

History
110 lines (87 loc) · 3.46 KB

use-openjson-with-the-default-schema-sql-server.md

File metadata and controls

110 lines (87 loc) · 3.46 KB
title Use OPENJSON with the Default Schema (SQL Server) | Microsoft Docs
ms.custom
SQL2016_New_Updated
ms.date 06/02/2016
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
dbe-json
ms.tgt_pltfrm
ms.topic article
helpviewer_keywords
OPENJSON, with default schema
ms.assetid 8e28a8f8-71a8-4c25-96b8-0bbedc6f41c4
caps.latest.revision 11
author douglaslMS
ms.author douglasl
manager craigg

Use OPENJSON with the Default Schema (SQL Server)

[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx_md]

Use OPENJSON with the default schema to returns a table with one row for each property of the object or for each element in the array.

Here are some examples that use OPENJSON with the default schema. For more info and more examples, see OPENJSON (Transact-SQL).

Example - Return each property of an object

Query

SELECT *
FROM OPENJSON('{"name":"John","surname":"Doe","age":45}')

Results

Key Value
name John
surname Doe
age 45

Example - Return each element of an array

Query

SELECT [key],value
FROM OPENJSON('["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]')

Results

Key Value
0 en-GB
1 en-UK
2 de-AT
3 es-AR
4 sr-Cyrl

Example - Convert JSON to a temporary table

The following query returns all properties of the info object.

DECLARE @json NVARCHAR(MAX)

SET @json=N'{  
     "info":{    
       "type":1,  
       "address":{    
         "town":"Bristol",  
         "county":"Avon",  
         "country":"England"  
       },  
       "tags":["Sport", "Water polo"]  
    },  
    "type":"Basic"  
 }'

SELECT *
FROM OPENJSON(@json,N'lax $.info')

Results

Key Value Type
type 1 0
address { "town":"Bristol", "county":"Avon", "country":"England" } 5
tags [ "Sport", "Water polo" ] 4

Example - Combine relational data and JSON data

In the following example, the SalesOrderHeader table has a SalesReason text column that contains an array of SalesOrderReasons in JSON format. The SalesOrderReasons objects contain properties like "Manufacturer" and "Quality". The example creates a report that joins every sales order row to the related sales reasons by expanding the JSON array of sales reasons as if the reasons were stored in a separate child table.

SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)

In this example, OPENJSON returns a table of sales reasons in which the reasons appear as the value column. The CROSS APPLY operator joins each sales order row to the rows returned by the OPENJSON table-valued function.

Learn more about the built-in JSON support in SQL Server

For lots of specific solutions, use cases, and recommendations, see the blog posts about the built-in JSON support in SQL Server and in Azure SQL Database by Microsoft Program Manager Jovan Popovic.

See Also

OPENJSON (Transact-SQL)