Skip to main content

Querying JSON data from SQL Server 2016

    
                                                                                            
Sources are heterogeneous and SQL Server allows various types of manipulations and one of the interesting feature in SQL Server 2016 is Querying data of JSON Format. There are four basic syntax which we can use against a JSON data type.

We can demonstrate the use of these syntax with a simple scenario, first off I will define a simple JSON array and walk through each syntax one by one.

Use of ISJSON() 

ISJSON() syntax is much similar to the ISNUMERIC() where the latter one checks for a numeric data type and outputs one if it’s true like wise ISJSON() out puts one if the evaluated value is in valid JSON Format.

Try This DEMO

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=N'{ 
     "info":{   
       "type":1, 
       "address":{   
         "town":"Colombo",  
         "country":"SriLanka" 
       }, 
       "tags":["Sport", "Water polo"] 
    }, 
    "type":"Basic" 
 }' 

 SELECT ISJSON(@jsonInfo)

Use of JSON_VALUE VS JSON_QUERY

Json value syntax looks for a specific value in the JSON array, here the path will start from a $ sign and the respective path root has to be configured, if there are multiple values or a sub array at the end of the path then JSON value will return NULL, it strictly checks for a single value where JSONQUERY() looks for an array and if it finds a single value at the end of the path then it will return NULL.

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=N'{ 
     "info":{   
       "type":1, 
       "address":{   
         "town":"Colombo",  
         "country":"SriLanka" 
       }, 
       "tags":["Sport", "Water polo"] 
    }, 
    "type":"Basic" 
 }' 

 select JSON_VALUE(@jsonInfo,'$.info.address.town') AS [Single value as JSON VALUE]
 select JSON_VALUE (@jsonInfo,'$.info') AS [Array as JSON VALUE]
 select JSON_QUERY(@jsonInfo,'$.info.address.town') AS [Single value as JSON QUERY]
 select JSON_QUERY(@jsonInfo,'$.info') AS [Array as JSON QUERY]

Use of JSON_MODIFY() 

Json modify allows you to modify a value in the end of an array path, so you can set a different value in an array, you can try the following demo

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=N'{ 
     "info":{   
       "type":1, 
       "address":{   
         "town":"Colombo",  
         "country":"SriLanka" 
       }, 
       "tags":["Sport", "Water polo"] 
    }, 
    "type":"Basic" 
 }' 

select JSON_VALUE(@jsonInfo,'$.info.address.town') AS [Past Town]

set @jsonInfo= JSON_MODIFY(@jsonInfo,'$.info.address.town','Jaffna')

select JSON_VALUE(@jsonInfo,'$.info.address.town') AS [Current Town]

 In the above scenario I'm setting a different value at the end of the array path and when I select it afterwards it gives the updated value as output.

 In a holistic view data manipulation is becoming more and more simple with such new additions, simply now if we have a data source in the form of JSON data type it can be easily queried from SSMS itself but again your version has to be 2016 or latter.




Comments

  1. meenatiOctober 18, 2019 at 5:51 AM


    I like your blog, I read this blog please update more content on hacking,Nice post
    tableau online training

    ReplyDelete

Post a Comment

Popular posts from this blog

Choosing color scheme for Power BI reports

Choosing beautiful colors for an attention seeking Power BI report is always challenging. Sometimes we may have an important message but it might go unseen if proper color scheme is not used, so it is important to create reports which are informational and visually appealing. In this blog our main objective is to get an idea about selecting appropriate color scheme and tools that can be beneficial to do this. What is a color scheme? As you all know not all colors look great together and you can tell stories by just having only two colors, have a look at the following image, there in the middle you can see a circle with same color and same dimension, only thing that changes is the back ground, but depending on the back ground the way you see those circles are totally different. Some small circles appear softer, some appear sharper, and some are brighter also you might notice movements or depth effect too. It is important to choose colors wisely to serve the purp...
Post a Comment
Read more

Ragged hierarchy in Power BI

Case- I have a data set which has to be visualized in a ragged hierarchy. What is ragged hierarchy A ragged hierarchy is a user defined hierarchy with an uneven number of levels.   You can see the above table which has 5 levels of hierarchy. In the first level we only have “World” as a category then moving forward to the next level we have two categories  namely “UK” and “USA” likewise the level of hierarchy is drilling down up to five levels with an uneven amount of categories. Creating a hierarchy in Power BI In the above image  you can notice that I have right clicked the “level 1” column which has to be the first level of our hierarchy, Once it is right clicked there you will notice “New hierarchy” as the second option then you will have to click that in order to create a new one. As you can notice in the above image once I have created a new hierarchy then a new column is created named “Level 1 Hierarchy” in that I have d...
3 comments
Read more

Identifying unused space consuming tables in SQL Server

  You might have an SQL Server database with lot of unnecessary tables. But you are unable to remove them because you don't know if it is still being used or not. Sometimes they have been created as a backup and not deleted so now it's there as a redundant and taking up your space. In order to identify such tables the below query comes in handy. You can check the Last Interaction date and the table size to determine what to do. As a best practice it is better to ha ve a last updated date as a column in tables to identify the current relevant tables. S ELECT   last_user_scan LastInteraction,   t.NAME AS TableName,   s.Name AS SchemaName,   p.rows AS RowsCount,   SUM(a.total_pages) * 8 AS TotalSpaceKB,   CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS numeric(36, 2)) AS TotalSpaceMB,   SUM(a.used_pages) * 8 AS UsedSpaceKB,   CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS numeric(36, 2)) AS UsedSpaceMB,   (SUM(a.total_pages) -...
Post a Comment
Read more