Tag Archives: JSON to rows in Sql

SQL Puzzle | Convert JSON Integer array to Table rows | SQL Interview Question

13 Tuesday Feb 2018

Posted by in SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Convert JSON Integer array to table rows | SQL Interview Question

In this puzzle you have to parse the JSON integer array to get data in a single column. Can you do that in a single select?. Please check the sample input and the expected output.

Notes –
1. The limitation is that we can only use a single SELECT for the puzzle.

Sample Input

Employees Table

jsonIntegerArray
[1,2,3,4,5,6,7,8,9]

Expected Output

Value
1
2
3
4
5
6
7
8
9

Use below script to create table and insert sample data into it.

--

DECLARE @jsonIntegerArray AS VARCHAR(MAX);
SET @jsonIntegerArray = '[1,2,3,4,5,6,7,8,9]';
SELECT @jsonIntegerArray

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1 | SOLUTION USING OPENJSON

--



DECLARE @jsonIntegerArray AS VARCHAR(MAX);
SET @jsonIntegerArray = '[1,2,3,4,5,6,7,8,9]';
SELECT Value from OPENJSON(@jsonIntegerArray)


--

Output-1

--                            

Value
-------------------------
1
2
3
4
5
6
7
8
9

(9 rows affected)


--

Related Posts

OPENJSON https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xii-openjson-function-sql-server/
ISJSON https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xiii-isjson/
JSON_VALUE https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xv-json_value/
JSON_MODIFY https://msbiskills.com/2018/01/23/new-t-sql-features-in-sql-server-2016-xvii-json_modify/

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

NEW T-SQL FEATURES IN SQL SERVER 2016 – XII | OPENJSON Function (SQL Server)

22 Monday Jan 2018

Posted by in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL SERVER, SQL Server Interview Questions

≈ 4 Comments

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


NEW T-SQL FEATURES IN SQL SERVER 2016 – XII | OPENJSON Function (SQL Server)

In this post we shall check out the new features Microsoft introduced in SQL Server 2016 | OPENJSON Function (SQL Server)

New feature – XII | OPENJSON Function (SQL Server)


From Microsoft – The OPENJSON rowset function converts JSON text into a set of rows and columns. After you transform a JSON collection into a rowset with OPENJSON, you can run any SQL query on the returned data or insert it into a SQL Server table.

The OPENJSON function takes a single JSON object or a collection of JSON objects and transforms them into one or more rows. By default, the OPENJSON function returns the following data:+
1. From a JSON object, the function returns all the key:value pairs that it finds at the first level.
2. From a JSON array, the function returns all the elements of the array with their indexes.

You can add an optional WITH clause to provide a schema that explicitly defines the structure of the output.

Notes

1. The OPENJSON function is available only under compatibility level 130. We can’t find and run the OPENJSON function below this
compatibility level. You can change the compatibility level of a database by using the following command:

--
	ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130
--

2. The OPENJSON has two flavors – default output and explicit structure.

3. OPENJSON with the default output

When you use the OPENJSON function without providing an explicit schema for the results – that is, without a WITH clause after OPENJSON – the function returns a table with the following three columns:

Key -> Name of the property(If Present) else index
value -> Property value(Data)
type -> An integer value means the type of the value.

4. OPENJSON output with an explicit structure

When you specify a schema for the results by using the WITH clause of the OPENJSON function, the function returns a table with only the columns that you define in the WITH clause. In the optional WITH clause, you specify a set of output columns, their types, and the paths of the JSON source properties for each output value. OPENJSON iterates through the array of JSON objects, reads the value on the specified path for each column, and converts the value to the specified type.

Examples

/* Using Option 1 – OPENJSON with the default output | Default Columns key, value and type */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
SELECT * FROM OPENJSON(@j)
GO

OUTPUT

key                         value                                         type
--------------------------- --------------------------------------------- ----
name                        Pawan                                         1
MiddleName                  Kumar                                         1
Surname                     Khowal                                        1
Class                       10                                            2
SkillSet                    ["SQL","SSIS","SSRS","SSAS"]                  4
IsDatabaseDeveloper         true                                          3

(6 rows affected)

--

/* Using Option 1 – OPENJSON with the default output | Columns key and value */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
SELECT [Key],[Value] FROM OPENJSON(@j)
GO

OUTPUT

Key                               Value                              
--------------------------------- ---------------------------------- 
name                              Pawan                              
MiddleName                        Kumar                              
Surname                           Khowal                             
Class                             10                                 
SkillSet                          ["SQL","SSIS","SSRS","SSAS"]       
IsDatabaseDeveloper               true                               
                                                                     
(6 rows affected)                                                    
                                                                     

--

/* Using Option 1 – OPENJSON with the default output | Get DATA FROM Object/Array from JSON without Property – SkillSet */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
SELECT [Key],[Value] FROM OPENJSON(@j,'$.SkillSet')
GO

OUTPUT

Key                   Value
--------------------- -----------
0                     SQL
1                     SSIS
2                     SSRS
3                     SSAS

(4 rows affected)


--

/* Using Option 1 – OPENJSON with the default output | Get DATA FROM Object/Array from JSON with Property – Add */

--

DECLARE @j NVARCHAR(MAX) 
SET @j = '{"Name":"Pawan","Add":{"State":"Illinois","Country":"Chicago"}}'
SELECT [Key],[Value] FROM OPENJSON(@j, '$.Add')
GO

OUTPUT

Key                   Value         
--------------------- ------------- 
State                 Illinois      
Country               Chicago       
                                    
(2 rows affected)                   
                                    
                      

--

/* Using Option 1 – OPENJSON with the default output | Get DATA FROM single object..Just to split the data */

--

DECLARE @j NVARCHAR(MAX) 
SET @j = '[1,2,8,121,121,12,8910,17]'
SELECT [Key],[Value] FROM OPENJSON(@j)
GO

OUTPUT

Key                        Value
-------------------------- --------------
0                          1
1                          2
2                          8
3                          121
4                          121
5                          12
6                          8910
7                          17

(8 rows affected)



--

/* Using Option 2 – OPENJSON output with an explicit structure | Get DATA FROM object..Add */

--


DECLARE @j NVARCHAR(MAX) 
SET @j = '{"Name":"Pawan","Add":{"State":"Illinois","Country":"Chicago"}}'
SELECT * FROM OPENJSON(@j, '$.Add')
WITH ( [State] VARCHAR(20) , [Country] VARCHAR(50) )
GO

OUTPUT

State                Country
-------------------- --------------------------------------------------
Illinois             Chicago

(1 row affected

--

/* Using Option 2 – OPENJSON output with an explicit structure | Get DATA FROM object..Add and Name from the outer data */

--

DECLARE @j NVARCHAR(MAX) 
SET @j = '{"Devs":[{"Name":"Pawan","Adda":{"State":"Illinois","Country":"Chicago"}}]}'
SELECT * FROM OPENJSON(@j,'$.Devs')
WITH ( [Name] VARCHAR(10) '$.Name', [State] VARCHAR(20) '$.Adda.State', [Country] VARCHAR(20) '$.Adda.Country' )
GO

OUTPUT

Name       State                Country
---------- -------------------- --------------------
Pawan      Illinois             Chicago

(1 row affected)


--

/* Using Option 2 – OPENJSON output with an explicit structure | Get DATA FROM object..Add and Name from the outer data */

--

DECLARE @j NVARCHAR(MAX) 
SET @j = '{"Name":"Pawan","Adda":{"State":"Illinois","Country":"Chicago"}}'
SELECT * FROM OPENJSON(@j)
WITH ( [Name] VARCHAR(10), [State] VARCHAR(20) '$.Adda.State', [Country] VARCHAR(20) '$.Adda.Country' )
GO

OUTPUT

Name       State                Country
---------- -------------------- --------------------
Pawan      Illinois             Chicago

(1 row affected)



--

Refer Microsoft LINK below for more details-

1. https://docs.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server
2. https://blogs.technet.microsoft.com/dataplatforminsider/2016/01/05/json-in-sql-server-2016-part-1-of-4/
3. https://docs.microsoft.com/en-us/sql/database-engine/whats-new-in-sql-server-2016#ForJson
4. https://docs.microsoft.com/en-us/sql/database-engine/whats-new-in-sql-server-2016

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com