Update/Delete JSON array value in SQL Server

Question

I have a json array in my table. It contains an array. I can create, append or make my array NULL. Inside my stored procedure but I don't see any way to pop value from array. Apparently JSON_Modify may have solution as you can update key as well as Single value but how can I use it to modify my array?

--My Array 
Declare @json = '{"array":[123,456]}'

Desired results after update:

'{"array":[123]}'

Please note that array contain int values. Which are my sub department id. All values are (supposed to be) unique.

Lukasz Szozda · Accepted Answer · 2018-04-28 13:33:33Z

1

You could use:

DECLARE @json NVARCHAR(MAX) = '{"array":[123,456]}';

WITH cte AS (
  SELECT *, MAX([key]) OVER() AS m_key
  FROM OPENJSON(@json, '$.array') s
)
SELECT JSON_QUERY('[' + IIF(MAX(m_key) = 0, '', STRING_AGG(value,',') 
                   WITHIN GROUP (ORDER BY [key])) + ']','$') AS array
FROM cte
WHERE [key] != m_key OR m_key = 0
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

Output:

{"array":[123]}

DBFiddle Demo SQL Server 2017

edited Apr 28, 2018 at 13:33

answered Apr 28, 2018 at 13:24

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

abdul qayyum Over a year ago

Work like a charm, though in hurry I completed my work using XML Path which is weird using XML with JSON.

Lukasz Szozda Over a year ago

@abdulqayyum Great to hear that you solved your question anyway :)

abdul qayyum Over a year ago

I have added my solution just for comparison.

vibs2006 Over a year ago

@lad2025 which is minimum version of SQL Server that supports OPENJSON ?

Lukasz Szozda Over a year ago

@abdulqayyum I modified my answer slightly to handle one element array. JSON is supported from SQL Server 2016

|

abdul qayyum · Accepted Answer · 2018-04-28 13:32:13Z

1

As I was in hurry I solved my problem following way, but I would really recommend not to use it. Please see answer above by @lad2025.

DECLARE @json VARCHAR(MAX)
                          =(SELECT jsonDept 
                            FROM tblEmployee 
                            WHERE tblEmployeeID = @empid)
    DECLARE @newjson VARCHAR(MAX)= (
    SELECT LEFT(subdept, LEN(subdept)-1)
    FROM (
    SELECT Distinct value + ', ' FROM OPENJSON(@json,'$.array') Where value <> @subdeptid
    FOR XML PATH ('')
    ) t (subdept))
    UPDATE tblEmployee SET jsonDept = '{"array":['+ @newjson +']}' WHERE tblEmployeeID = @empid

answered Apr 28, 2018 at 13:32

abdul qayyum

5651 gold badge18 silver badges41 bronze badges

Collectives™ on Stack Overflow

Update/Delete JSON array value in SQL Server

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related