use-openjson-with-an-explicit-schema-sql-server.md

description	Use OPENJSON with an Explicit Schema (SQL Server)
title	Use OPENJSON with an Explicit Schema
ms.date	06/03/2020
ms.service	sql
ms.subservice
ms.topic	conceptual
helpviewer_keywords	OPENJSON, with explicit schema
ms.assetid	9c1c3bfb-e1ad-4659-b94f-722b0848d5a2
author	jovanpop-msft
ms.author	jovanpop
ms.reviewer	jroth
ms.custom	seo-dt-2019
monikerRange	=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Use OPENJSON with an Explicit Schema (SQL Server)

[!INCLUDE SQL Server Azure SQL Database]

Use OPENJSON with an explicit schema to return a table that's formatted as you specify in the WITH clause.

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

Example - Use the WITH clause to format the output

The following query returns the results shown in the following table. Notice how the AS JSON clause causes values to be returned as JSON objects instead of scalar values in col5 and array_element.

DECLARE @json NVARCHAR(MAX) =
N'{"someObject":   
    {"someArray":  
      [  
          {"k1": 11, "k2": null, "k3": "text"},  
          {"k1": 21, "k2": "text2", "k4": { "data": "text4" }},  
          {"k1": 31, "k2": 32},  
          {"k1": 41, "k2": null, "k4": { "data": false }}     
       ]  
    }  
 }'  
   
SELECT * FROM  
 OPENJSON(@json, N'lax $.someObject.someArray')  
WITH ( k1 int,   
        k2 varchar(100),  
        col3 varchar(6) N'$.k3',  
        col4 varchar(10) N'lax $.k4.data',  
        col5 nvarchar(MAX) N'lax $.k4' AS JSON, 
        array_element nvarchar(MAX) N'$' AS JSON  
 )  

Results

k1	k2	col3	col4	col5	array_element
11	NULL	"text"	NULL	NULL	{"k1": 11, "k2": null, "k3": "text"}
21	"text2"	NULL	"text4"	{ "data": "text4" }	{"k1": true, "k2": "text2", "k4": { "data": "text4" } }
31	"32"	NULL	NULL	NULL	{"k1": 31, "k2": 32 }
41	NULL	NULL	false	{ "data": false }	{"k1": 41, "k2": null, "k4": { "data": false } }

Example - Load JSON into a [!INCLUDEssNoVersion] table.

The following example loads an entire JSON object into a [!INCLUDEssNoVersion] table.

DECLARE @json NVARCHAR(MAX) = '{  
  "id" : 2,  
  "firstName": "John",  
  "lastName": "Smith",  
  "isAlive": true,  
  "age": 25,  
  "dateOfBirth": "2015-03-25T12:00:00",  
  "spouse": null  
  }';  
   
  INSERT INTO Person  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id int,  
        firstName nvarchar(50), lastName nvarchar(50),   
        isAlive bit, age int,  
        dateOfBirth datetime2, spouse nvarchar(50))  

Learn more about JSON in SQL Server and Azure SQL Database

Microsoft videos

For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

• JSON as a bridge between NoSQL and relational worlds

See Also

OPENJSON (Transact-SQL)