Skip to content

Latest commit

 

History

History
91 lines (73 loc) · 3.33 KB

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

File metadata and controls

91 lines (73 loc) · 3.33 KB
title description author ms.author ms.reviewer ms.date ms.service ms.topic helpviewer_keywords monikerRange
Use OPENJSON with an Explicit Schema
Use OPENJSON with an Explicit Schema (SQL Server)
jovanpop-msft
jovanpop
jroth
03/06/2025
sql
language-reference
OPENJSON, with explicit schema
=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

Use OPENJSON with an Explicit Schema

[!INCLUDE sqlserver2016-asdb-asdbmi-asa-serverless-pool-only-fabricse-fabricdw]

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 the SQL Database Engine

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

Related content