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

Expected Output

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

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.

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