How to read json in Sql Server | Code2night.com
Code2night
  • Home
  • Blogs
  • Guest Posts
  • Tutorial
  • Post Blog
  • Register
  • Login
  1. Home
  2. Blogpost

How to read json in Sql Server

Date- Aug 10,2022

3786

OpenJson Sql server

JSON in Sql Server

So , you can store json data in any Nvarchar variable in sql server procedure or query. But for reading or deserializing that json data we have to use OpenJson . OpenJson function is provided by sql server for making us able to use json data in normal queries. So , we will have a look at the example below with sample json data.


 DECLARE @JSONData NVARCHAR(MAX)='[
	{
		"color": "red",
		"value": "#f00"
	},
	{
		"color": "green",
		"value": "#0f0"
	},
	{
		"color": "blue",
		"value": "#00f"
	}
	
]'

 SELECT * 
 FROM OPENJSON(@JSONData) WITH (color NVARCHAR(100),value NVARCHAR(100)) 

Here, you can see we have stored json data in a Nvarchar variable and then in the select query we have used OpenJson function. 

In the WITH (color NVARCHAR(100),value NVARCHAR(100))   we are actually telling which properties are there in the json array, you will get those in the select query , we the assigned values to those properties so the output will look like

You can see the result from json in the table format. Sometimes you might now to read json from nested json also , so we will see how to read nested json using OpenJson.

Nested json using OpenJson

So, for reading nested json we can have a look at following example


DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "lang":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json,'$.path.to.lang')

Here, we can see we have passed nested json and when we do that we can pass additional parameter to OpenJson to let it know which property we want to read. So the output in this case will be

So, this is how to read json in sql server.

Comments

Tags

LinkedinLogin
LinkedinProfile
GetLinkedinProfile
C#
Aspnet
MVC
Linkedin
ITextSharp
Export to Pdf
AspNet Core
AspNet
View to Pdf in Aspnet
Model Validation In ASPNET Core MVC 60
Model Validation
Model Validation In ASPNET Core MVC
Model Validation In ASPNET
Image Compression in AspNet
Compress Image in c#
AspNet MVC
Free Download for Youtube Subscribers!

First click on Subscribe Now and then subscribe the channel and come back here.
Then Click on "Verify and Download" button for download link

Subscribe Now | 1190
Download
Support Us....!

Please Subscribe to support us

Thank you for Downloading....!

Please Subscribe to support us

Continue with Downloading
Be a Member
Join Us On Whatsapp Join Us On Facebook

Welcome To Code2night, A common place for sharing your programming knowledge,Blogs and Videos

  • Panipat
  • info@Code2night.com

Links

  • Home
  • Blogs
  • Tutorial
  • Post Blog

Popular Tags

Copyright © 2025 by Code2night. All Rights Reserved

  • Home
  • Blog
  • Login
  • SignUp
  • Contact
  • Terms & Conditions
  • Refund Policy
  • About Us
  • Privacy Policy
  • Json Beautifier
  • Guest Posts