SQL PUZZLE | Remove all JSON elements BUT first | Advanced SQL

In this puzzle you have to delete all values from the JSON array but first. Please see the sample input and expected output.

Sample Input

Expected Output

Script – DDL and INSERT Sample Data

--

DECLARE @jsonstr NVARCHAR(MAX) = '{"a":[1,2,3,4,5,6]}'

--

SOLUTION – 1

--

DECLARE @jsonstr NVARCHAR(MAX) = '{"a":[1,2,3,4,5,6]}'

;WITH CTE AS 
(
  SELECT * FROM OPENJSON(@jsonstr, '$.a')
)
SELECT JSON_QUERY( CONCAT ( '[',STRING_AGG([Value],',') WITHIN GROUP (ORDER BY [key]) ,']') ,'$') a
FROM CTE
WHERE [key] = 0
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
GO

--

Output – 1

--

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
------------------------------------------------------
{"a":[1]}

(1 row affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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