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

Advertisements