Skip to content

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

Latest commit

 

History

History
93 lines (79 loc) · 3.43 KB

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

File metadata and controls

93 lines (79 loc) · 3.43 KB
title Use OPENJSON with an Explicit 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 explicit schema
ms.assetid 9c1c3bfb-e1ad-4659-b94f-722b0848d5a2
caps.latest.revision 13
author douglaslMS
ms.author douglasl
manager jhubbard

Use OPENJSON with an Explicit Schema (SQL Server)

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

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 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)